Page 1 of 1

There has to be a better way

Posted: Sun Nov 11, 2007 6:00 pm
by akak
How do I create a list from a query? The only way I can figure doing it is using a loop. Is there a better/easier way?

--------------------------------------------------------------------------------------------------------------

Code: Select all

$query = "SELECT productid, productname FROM products WHERE categoryid = 1";

$listproductid = '';
	
while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {

	$listproductid .= $row['productid'] . ', ';

}

$listproductid = substr($listproductid , 0, -2);

echo $listproductid;
----------------------------------------------------------------------------------------------------------------

Has to be a better way.

Thanks in advance.

Posted: Sun Nov 11, 2007 6:07 pm
by s.dot
Yes.

Code: Select all

$listproductid = array();
while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {

    $listproductid[] = $row['productid'];

}

$listproductid = implode(',', $listproductid);

echo $listproductid
Have a look at the function implode().

Posted: Sun Nov 11, 2007 6:23 pm
by Bogey
Such as the following???

Code: Select all

<?php
$result = mysql_query("SELECT productid, productname FROM products WHERE categoryid = '1'") or die(mysql_error());

$tsv  = array();
$html = array();
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
   $tsv[]  = implode("\t", $row);
   $html[] = "<li>" .implode("", $row) .              "</li>";
   $htmm = " " . implode("", $row) ." ";
}

$tsv = implode("\r\n", $tsv);
$html = '<ol>'. implode("\r\n", $html);



//echo $tsv;
echo $html;

echo $row .'</ol>';
?>
...or...

Code: Select all

<?php
$result = mysql_query("SELECT productid, productname FROM products WHERE categoryid = '1'") or die(mysql_error());

$tsv  = array();
$html = array();
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
   $tsv[]  = implode("\t", $row);
   $html[] = "<li>" .implode(" | ", $row) .              "</li>";
   $htmm = " " . implode(" | ", $row) ." ";
}

$tsv = implode("\r\n", $tsv);
$html = '<ol>'. implode("\r\n", $html);



//echo $tsv;
echo $html;

echo $row .'</ol>';
?>
...or even...

Code: Select all

$result = mysql_query("SELECT productid, productname FROM products WHERE categoryid = '1'") or die(mysql_error());

$tsv  = array();
$html = array();
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
   $tsv[]  = implode("\t", $row);
   $html[] = "<tr><td>" .implode("</td><td>", $row) .              "</td></tr>";
   $htmm = "<br />" . implode("</td><td>", $row) ."<br />";
}

$tsv = implode("\r\n", $tsv);
$html = '<table border="1" width="100%"><tr><td bgcolor="#FFCC00">Name</td><td bgcolor="#FFCC00">Gender</td><td bgcolor="#FFCC00">Birthday</td><td bgcolor="#FFCC00">Secret Question</td><td bgcolor="#FFCC00">Secret Answer</td><td bgcolor="#FFCC00">Level</td></tr>' . implode("\r\n", $html) . '<br /></table>';



//echo $tsv;
echo $html;

echo '<table><tr><td>'. $row .'</td></tr></table>';
All of them work :)

Posted: Sun Nov 11, 2007 6:41 pm
by akak
Wow. Thanks. I always wondered what that "implode" would be useful for.

Now, here a follow-up question to the postings....

Since I have to use the while statement to populate the array, how do I re-execute the query further down the page? In ASP, there was something like a "movefirst" command (or something like that). Is there something similar in PHP?

Code: Select all

$query = "SELECT productid, productname FROM products WHERE categoryid = 1"; 

$listproductid = array();

while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {

    $listproductid[] = $row['productid'];

}

$listproductid = implode(',', $listproductid);

echo $listproductid;

// Re-execute query, but now print out product names
while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {
     
     echo $row['productname'];

}
Thanks again!

Posted: Sun Nov 11, 2007 6:46 pm
by Bogey
You mean like do that over and over again?

Try...

Code: Select all

function showRow() {

$query = "SELECT productid, productname FROM products WHERE categoryid = 1";

$listproductid = array();

while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {

    $listproductid[] = $row['productid'];

}

$listproductid = implode(',', $listproductid);

echo $listproductid;

// Re-execute query, but now print out product names
while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {
     
     echo $row['productname'];

} 
}
?>
<br /><br /><br />
<p>blahblahblah<br />
blahblahblah</p>
<?php
showRow();
?>
<br /><br /><br />
<p>blahblahblah<br />
blahblahblah</p>
<?php
showRow();
?>
<br /><br /><br />
<p>blahblahblah<br />
blahblahblah</p>
<?php
showRow();
?>
With the top code just put the following code where you want the rows to show... (notice the whole big piece won't show by itself... u need the following code... the big piece of code has to be above the first one of the following code...

Code: Select all

showRow();

Posted: Sun Nov 11, 2007 7:21 pm
by akak
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Sorry, I worded my 2nd question incorrectly (pardon my technical ignorance).  

Actually, what I'm trying to do is re-loop the results w/o re-executing the query.  By creating a function, I believe you re-execute the query (which I'm trying to avoid sending the 2nd (or multiple) request(s) to the database).  I wanted to execute the query only once and "re-use" the query results as many times as I want in the page.  

With the code below, the first [b]while [/b] loop will populate the list ($listproductid[])...but the 2nd [b]while[/b] loop (of product names) will be empty...because the "pointer" is at the bottom of the query result (or something like that).

Code: Select all

$query = "SELECT productid, productname FROM products WHERE categoryid = 1";

$listproductid = array();

while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {

    $listproductid[] = $row['productid'];

}

$listproductid = implode(',', $listproductid);

echo $listproductid;

// Re-execute query, but now print out product names....this will not work since the pointer is at the bottom of the $row array.
while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {
     
     echo $row['productname'];
Thanks again in advance.


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Sun Nov 11, 2007 7:44 pm
by Bogey
You can do that in my previous examples (three examples... my first reply here).. that is why there is echo $html; there... the $html is the results... let me work out on your example here...

Try the following...

Code: Select all

$query = "SELECT productid, productname FROM products WHERE categoryid = 1";

$listproductid = array();

while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {

    $listproductid[] = $row['productid'];

}

$listproductid = implode(',', $listproductid);
$listproductname =  $row['productname'];

echo $listproductid .'<br />';
echo $listproductname .'<br />';
If you can store two variables into one (I'm not sure if you can and if you can than I don't know how), store $listproductid and $listproductname into one and than you have one echo or store both echo statements into a function such as...

Code: Select all

$query = "SELECT productid, productname FROM products WHERE categoryid = 1";

$listproductid = array();

while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {

    $listproductid[] = $row['productid'];

}

$listproductid = implode(',', $listproductid);
$listproductname =  $row['productname'];

function showRow {
echo $listproductid .'<br />';
echo $listproductname .'<br />';
}
showRow();
And then you know what to do with the function showRow and how to implement that I'm assuming. Hope that this helps...

Posted: Sun Nov 11, 2007 8:08 pm
by akak
I tried running this (using your 1st code example), but it didn't work...

The echo $listproductid did generate the list of productid's, but the echo $listproductname came out blank (empty). I believe the reason is that the pointer to the query result ($row) is at the bottom of the query result (after going thru the first while loop) and thus there is no more $row['productname'] to assign it to the variable $listproductname.

Help??

Posted: Sun Nov 11, 2007 8:14 pm
by Bogey
:lol: I don't know about this... try it...

Code: Select all

$query = "SELECT productid, productname FROM products WHERE categoryid = 1";

$listproductid = array();

while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {

    $listproductid[] = $row['productid'];
    $listproductname[] = $row['productname'];

}

$listproductid = implode(',', $listproductid);
$listproductname =  implode(',', $listproductname);

function showRow {
echo $listproductid .'<br />';
echo $listproductname .'<br />';
}
showRow();

Posted: Sun Nov 11, 2007 8:59 pm
by s.dot
To move the result set back to the first row, in case you need to loop through the query again, use mysql_data_seek()

Code: Select all

mysql_data_seek($resultset, 0);

Posted: Sun Nov 11, 2007 10:01 pm
by akak
Exactly what I was looking for! Thanks!!

Posted: Sun Nov 11, 2007 10:32 pm
by John Cartwright
Very rarely should you have to re-execute the query. Store the results from the initial query (you are after all already fetching that data)