There has to be a better way

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
akak
Forum Newbie
Posts: 5
Joined: Sun Nov 11, 2007 5:48 pm

There has to be a better way

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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().
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Bogey
Forum Commoner
Posts: 34
Joined: Sat Nov 10, 2007 5:51 pm

Post 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 :)
akak
Forum Newbie
Posts: 5
Joined: Sun Nov 11, 2007 5:48 pm

Post 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!
Bogey
Forum Commoner
Posts: 34
Joined: Sat Nov 10, 2007 5:51 pm

Post 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();
akak
Forum Newbie
Posts: 5
Joined: Sun Nov 11, 2007 5:48 pm

Post 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]
Bogey
Forum Commoner
Posts: 34
Joined: Sat Nov 10, 2007 5:51 pm

Post 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...
akak
Forum Newbie
Posts: 5
Joined: Sun Nov 11, 2007 5:48 pm

Post 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??
Bogey
Forum Commoner
Posts: 34
Joined: Sat Nov 10, 2007 5:51 pm

Post 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();
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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);
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
akak
Forum Newbie
Posts: 5
Joined: Sun Nov 11, 2007 5:48 pm

Post by akak »

Exactly what I was looking for! Thanks!!
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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)
Post Reply