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

Code: Select all

<?php 

echo $product_list; 

?>
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