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...
Posted: Sun Nov 11, 2007 7:21 pm
by akak
feyd | Please use 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
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

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()
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)