Dreamweaver - OT: How do you group SQL queries? (PHP)

Author

OT: How do you group SQL queries? (PHP)

David B

2004-06-11, 7:15 pm

The code below contains three SQL queries. I've learned that I can
reduce it to one query if I change the first query from $res =
mysql_query ("SELECT Name FROM basics WHERE AreaID like '$mycode'")

to $res = mysql_query ("SELECT Name, Capital, Population FROM basics
WHERE AreaID like '$mycode'")

In fact, I've already changed it in the example below. But how do I cut
out the other two queries?

I can't reduce this...

{
$res = mysql_query ("SELECT Population FROM basics WHERE AreaID like
'$mycode'") or die (mysql_error());
while (list($name) = mysql_fetch_row($res))
{ echo "$name\n"; }
}

to this...

{ echo "$name\n"; }

and none of the other variations I've tried work.

[PHP]
<?php

echo '<div class="subtitle" id="sub' . $mycode . '">';
{
$res = mysql_query ("SELECT Name, Capital, Population FROM basics WHERE
AreaID like '$mycode'") or die (mysql_error());
while (list($name) = mysql_fetch_row($res))
{ echo "$name\n"; }
}

echo '</div>
<div class="quote2" id="q2' . $mycode . '">
<div class="quote" id="q' . $mycode . '">“';

{
$res = mysql_query ("SELECT Capital FROM basics WHERE AreaID like
'$mycode'") or die (mysql_error());
while (list($name) = mysql_fetch_row($res))
{ echo "$name\n"; }
}

echo '”</div>
<div class="sig" id="sig' . $mycode . '">';

{
$res = mysql_query ("SELECT Population FROM basics WHERE AreaID like
'$mycode'") or die (mysql_error());
while (list($name) = mysql_fetch_row($res))
{ echo "$name\n"; }
}

echo '</div>
</div>';

?>
[/PHP]

Michael Fesser

2004-06-11, 7:15 pm

.oO(David B)

>The code below contains three SQL queries. I've learned that I can
>reduce it to one query if I change the first query from $res =
>mysql_query ("SELECT Name FROM basics WHERE AreaID like '$mycode'")
>
>to $res = mysql_query ("SELECT Name, Capital, Population FROM basics
>WHERE AreaID like '$mycode'")
>
>In fact, I've already changed it in the example below. But how do I cut
>out the other two queries?

Just an idea: Use the query to fetch all informations, then store them
in an associative array. Whenever you need to output the infos just
iterate over this array instead of sending another query to the db:

$query = "
SELECT Name, Capital, Population
FROM basics
WHERE AreaID like '$mycode'";
$data = array();
if ($res = mysql_query($query)) {
while ($temp = mysql_fetch_assoc($res)) {
$data[] = $temp;
}
}

Now $data should contain all the informations.

Instead of

>$res = mysql_query ("SELECT Population FROM basics WHERE AreaID like
>'$mycode'") or die (mysql_error());
>while (list($name) = mysql_fetch_row($res))
> { echo "$name\n"; }
>}

simply write

reset($data);
while ($r = each($data)) {
print $r[1]['Population'];
}

or

foreach ($data as $r) {
print $r['Population'];
}

Instead of

>$res = mysql_query ("SELECT Capital FROM basics WHERE AreaID like
>'$mycode'") or die (mysql_error());
>while (list($name) = mysql_fetch_row($res))
> { echo "$name\n"; }
>}

simply write

reset($data);
while ($r = each($data)) {
print $r[1]['Capital'];
}

or

foreach ($data as $r) {
print $r['Capital'];
}

HTH
Micha

David B

2004-06-11, 11:14 pm

Michael Fesser wrote:

> .oO(David B)
>
> Just an idea: Use the query to fetch all informations, then store them
> in an associative array. Whenever you need to output the infos just
> iterate over this array instead of sending another query to the db:
>
> $query = "
> SELECT Name, Capital, Population
> FROM basics
> WHERE AreaID like '$mycode'";
> $data = array();
> if ($res = mysql_query($query)) {
> while ($temp = mysql_fetch_assoc($res)) {
> $data[] = $temp;
> }
> }
>
> Now $data should contain all the informations.
>
>
> Instead of
>
>
>
>
> simply write
>
> reset($data);
> while ($r = each($data)) {
> print $r[1]['Population'];
> }
>
> or
>
> foreach ($data as $r) {
> print $r['Population'];
> }
>
>
> Instead of
>
>
>
>
> simply write
>
> reset($data);
> while ($r = each($data)) {
> print $r[1]['Capital'];
> }
>
> or
>
> foreach ($data as $r) {
> print $r['Capital'];
> }

Wow, that was an amazing response. You solved my problem and taught me
how to make an array at the same time, and I never encountered a single
bug or "parse error" while plugging it all in!

I think what you set up for me is what I've been trying to do from the
very beginning. Anyway, thanks for all the help you and Gary White have
given me. I expect to launch my new websites in the next two or three
days. :)

Michael Fesser

2004-06-11, 11:14 pm

.oO(David B)

>I think what you set up for me is what I've been trying to do from the
>very beginning. Anyway, thanks for all the help you and Gary White have
>given me. I expect to launch my new websites in the next two or three
>days. :)

No problem and good luck. :D

Micha