Page 1 of 2
[SOLVED] - MYSQL_QUERY Question - Driving me nuts
Posted: Mon May 17, 2004 1:21 pm
by reverend_ink
OK I checked results in phpmyadmin sql query, returns results fine, but when I try to get it to work in php it fails.
I didnt write the code, am fixing it for a cleint.
Code: Select all
<?php
function show_products($maingroup) {
$query="
select cat_trans.catid,product.name,product.id from cat_trans,product_trans,product where cat_trans.catid='$maingroup' and cat_trans.catid=product_trans.catid and
product_trans.productid=product.id order by product.name asc";
$results=mysql_query($query);
$x=1;
while($row=mysql_fetch_array($results)) {
if($x == "1") {
$product_list.="<tr><td><a href='productinfo.php?id=$row[2]'><FONT size="2" face="Verdana" color="#6699ff"><B>$row[1]</b></a></td>";
$x++;
} elseif ($x == "2") {
$product_list.="<td><a href='productinfo.php?id=$row[2]'><FONT size="2" face="Verdana" color="#6699ff"><B>$row[1]</b></a></td>";
$x++;
} elseif($x =="3") {
$product_list.="<td><a href='productinfo.php?id=$row[2]'><FONT size="2" face="Verdana" color="#6699ff"><B>$row[1]</b></a></td></tr>";
}
}
return $product_list;
mysql_free_result($results);
}
?>
On the page I have
I am pulling my hair out as I know it has to be somethign simple I am missing.
THanks
Posted: Mon May 17, 2004 1:27 pm
by feyd
Code: Select all
<?php
echo show_products('product name here');
?>
Posted: Mon May 17, 2004 1:28 pm
by markl999
How is it failing exactly?
I presume you have something like :
$product_list = show_products(2);
echo $product_list;
?
Posted: Mon May 17, 2004 1:28 pm
by leenoble_uk
Where's your db connection coming from?
your query is acting inside a function so you need to put
global $db;
or whatever the name of your mysql connection is on the first line of the function.
you'll also need to do a mysql_select_db thing as well but this doesn't need to be inside the function.
Posted: Mon May 17, 2004 1:33 pm
by reverend_ink
leenoble_uk wrote:Where's your db connection coming from?
your query is acting inside a function so you need to put
global $db;
or whatever the name of your mysql connection is on the first line of the function.
you'll also need to do a mysql_select_db thing as well but this doesn't need to be inside the function.
global $sql
Posted: Mon May 17, 2004 1:34 pm
by reverend_ink
markl999 wrote:How is it failing exactly?
I presume you have something like :
$product_list = show_products(2);
echo $product_list;
?
It returns nothing
All the rest of the functions return their info properly.
As I said before this isnt my code, I am just trying to make it work.
Posted: Mon May 17, 2004 1:42 pm
by leenoble_uk
Code: Select all
<?php
$result = mysql_query($query, $sql); //if $sql is the name of your db connection
?>
Posted: Mon May 17, 2004 1:43 pm
by reverend_ink
leenoble_uk wrote:Code: Select all
<?php
$result = mysql_query($query, $sql); //if $sql is the name of your db connection
?>
It connects fine,
the error is after the WHILE statement.
Somewhere in here
Code: Select all
<?php$x=1;
while($row=mysql_fetch_array($results)) {
if($x == "1") {
$product_list.="<tr><td><a href='productinfo.php?id=$row[2]'><FONT size="2" face="Verdana" color="#6699ff"><B>$row[1]</b></a></td>";
$x++;
} elseif ($x == "2") {
$product_list.="<td><a href='productinfo.php?id=$row[2]'><FONT size="2" face="Verdana" color="#6699ff"><B>$row[1]</b></a></td>";
$x++;
} elseif($x =="3") {
$product_list.="<td><a href='productinfo.php?id=$row[2]'><FONT size="2" face="Verdana" color="#6699ff"><B>$row[1]</b></a></td></tr>";
}
}
return $product_list;
?>
Posted: Mon May 17, 2004 1:46 pm
by leenoble_uk
Could it be something to do with your numbers being treated as strings?
You don't need to put numbers inside quotes.
if($x==1) etc...
It may be comparing 1 to "1" AS A STRING which isn't the same.
Posted: Mon May 17, 2004 1:48 pm
by reverend_ink
leenoble_uk wrote:Could it be something to do with your numbers being treated as strings?
You don't need to put numbers inside quotes.
if($x==1) etc...
It may be comparing 1 to "1" AS A STRING which isn't the same.
Had already tried that.
I am at a lose, I havent had a problem like this happen before.
Posted: Mon May 17, 2004 1:50 pm
by reverend_ink
I have even brought it down to a lower form to see if it was the X = statement
Code: Select all
<?php
while($row=mysql_fetch_array($results)) {
$product_list.="<tr><td><a href='productinfo.php?id=$row[2]'><FONT size="2" face="Verdana" color="#6699ff"><B>$row[1]</b></a></td>";
}
?>
Posted: Mon May 17, 2004 1:51 pm
by leenoble_uk
Hmm.
Do you need x++ in there twice.
It's in a while loop so at first iteration it would do x==1 there's no reason to increment x there.
Try just doing x++ AFTER the if clauses so its.
Code: Select all
<?php
$x=1;
while($row=mysql_fetch_array($results))
{
if($x == "1")
{
$product_list.="<tr><td><a href='productinfo.php?id=$row[2]'><FONT size="2" face="Verdana" color="#6699ff"><B>$row[1]</b></a></td>";
}
elseif ($x == "2")
{
$product_list.="<td><a href='productinfo.php?id=$row[2]'><FONT size="2" face="Verdana" color="#6699ff"><B>$row[1]</b></a></td>";
}
elseif($x =="3")
{
$product_list.="<td><a href='productinfo.php?id=$row[2]'><FONT size="2" face="Verdana" color="#6699ff"><B>$row[1]</b></a></td></tr>";
}
$x++;
}
?>
Posted: Mon May 17, 2004 1:51 pm
by magicrobotmonkey
print_r($row) in the loop and see what its got in it
Posted: Mon May 17, 2004 1:55 pm
by markl999
Does the following version make any difference?
Code: Select all
<?php
function show_products($maingroup) {
$query= "select cat_trans.catid,product.name,product.id from cat_trans,product_trans,product where cat_trans.catid='$maingroup' and cat_trans.catid=product_trans.catid and product_trans.productid=product.id order by product.name asc";
$results=mysql_query($query) or die(mysql_error());
$x=1;
$product_list = '';
if(mysql_num_rows($results)){
while($row=mysql_fetch_array($results)) {
if($x == 1) {
$product_list .= '<tr><td><a href="productinfo.php?id='.$row[2].'"><FONT size="2" face="Verdana" color="#6699ff"><B>'.$row[1].'</b></a></td>';
} elseif ($x == 2) {
$product_list .= '<td><a href="productinfo.php?id='.$row[2].'"><FONT size="2" face="Verdana" color="#6699ff"><B>'.$row[1].'</b></a></td>';
} elseif($x == 3) {
$product_list .= '<td><a href="productinfo.php?id='.$row[2].'"><FONT size="2" face="Verdana" color="#6699ff"><B>'.$row[1].'</b></a></td></tr>';
}
$x++;
}
} else {
echo 'No results found.';
}
mysql_free_result($results);
return $product_list;
}
?>
Posted: Mon May 17, 2004 1:58 pm
by reverend_ink
markl999 wrote:Does the following version make any difference?
Code: Select all
<?php
function show_products($maingroup) {
$query= "select cat_trans.catid,product.name,product.id from cat_trans,product_trans,product where cat_trans.catid='$maingroup' and cat_trans.catid=product_trans.catid and product_trans.productid=product.id order by product.name asc";
$results=mysql_query($query) or die(mysql_error());
$x=1;
$product_list = '';
if(mysql_num_rows($results)){
while($row=mysql_fetch_array($results)) {
if($x == 1) {
$product_list .= '<tr><td><a href="productinfo.php?id='.$row[2].'"><FONT size="2" face="Verdana" color="#6699ff"><B>'.$row[1].'</b></a></td>';
} elseif ($x == 2) {
$product_list .= '<td><a href="productinfo.php?id='.$row[2].'"><FONT size="2" face="Verdana" color="#6699ff"><B>'.$row[1].'</b></a></td>';
} elseif($x == 3) {
$product_list .= '<td><a href="productinfo.php?id='.$row[2].'"><FONT size="2" face="Verdana" color="#6699ff"><B>'.$row[1].'</b></a></td></tr>';
}
$x++;
}
} else {
echo 'No results found.';
}
mysql_free_result($results);
return $product_list;
}
?>
Mark I dont know why I didnt put mysql_num_rows in before....
it displays no results found.
which is odd because when I run an sql statement in phpmyadmin it works...
this is odd