[SOLVED] - MYSQL_QUERY Question - Driving me nuts

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

reverend_ink
Forum Contributor
Posts: 151
Joined: Sun Apr 20, 2003 1:18 am
Location: Las Vegas | London

[SOLVED] - MYSQL_QUERY Question - Driving me nuts

Post 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
Last edited by reverend_ink on Mon May 17, 2004 3:00 pm, edited 2 times in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

<?php 
echo show_products('product name here'); 
?>
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

How is it failing exactly?
I presume you have something like :
$product_list = show_products(2);
echo $product_list;

?
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

Post 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.
reverend_ink
Forum Contributor
Posts: 151
Joined: Sun Apr 20, 2003 1:18 am
Location: Las Vegas | London

Post 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
reverend_ink
Forum Contributor
Posts: 151
Joined: Sun Apr 20, 2003 1:18 am
Location: Las Vegas | London

Post 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.
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

Post by leenoble_uk »

Code: Select all

<?php
$result = mysql_query($query, $sql); //if $sql is the name of your db connection

?>
reverend_ink
Forum Contributor
Posts: 151
Joined: Sun Apr 20, 2003 1:18 am
Location: Las Vegas | London

Post 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;
?>
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

Post 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.
reverend_ink
Forum Contributor
Posts: 151
Joined: Sun Apr 20, 2003 1:18 am
Location: Las Vegas | London

Post 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.
reverend_ink
Forum Contributor
Posts: 151
Joined: Sun Apr 20, 2003 1:18 am
Location: Las Vegas | London

Post 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>";
  

  
  
 }


?>
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

Post 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++;
} 


?>
Last edited by leenoble_uk on Mon May 17, 2004 1:58 pm, edited 1 time in total.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

print_r($row) in the loop and see what its got in it
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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;
}

?>
reverend_ink
Forum Contributor
Posts: 151
Joined: Sun Apr 20, 2003 1:18 am
Location: Las Vegas | London

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