Help & FAQs    Search BCF    Members    Groups    Events Calendar
Resend my activation email : Register : Log in 
BCF: Bike Chat Forums


SQL help needed

 Reply to topic
Bike Chat Forums Index -> The Geek Zone
View previous topic : View next topic  
Author Message

AcIdBuRnZ
World Chat Champion



Joined: 28 Jul 2003
Karma :
PostPosted: 04:48 - 23 Mar 2006    Post subject: SQL help needed Reply with quote
Hi,

This has been doing my head in for hours....

It's failing at the line in bold

Quote:
<?php

$department=$_POST['department'];
$manufacturer=$_POST['manufacturer'];
$name=$_POST['name'];

if (!$manufacturer)
{
echo '<center><hr>You must enter search criteria<br/>'
.'Please go back and try again<hr></center>';
exit;
}

mysql_pconnect('localhost', 'root', '') or die("Unable to connect to database");

mysql_select_db('valentines');
$sqlquery = 'SELECT * FROM $department WHERE manufacturer = $manufacturer';
$results = mysql_query($sqlquery)
or die("Could not execute query, please try again later");

while ($row = mysql_fetch_array($results)){
echo '<table width="100%" cellpadding="5" border="0">
<tr>
<td colspan=2 width=100% bgcolor=#990000><font color=#FFFFFF>'.$row[name].'</font></td></tr>
<tr>
<td rowspan=4><a href='.$row[image].'><img src='.$row[image].' height="150" width="150" /></a></td>
<td>Description : '.$row[description].'</td>
</tr>
<tr>
<td>Colours : '.$row[colours].'</td>
</tr>
<tr>
<td>Sizes : '.$row[sizes].'</td>
</tr>
<tr>
<td>Price : '.$row[price].'</td>
</tr>
</table>';}

if(mysql_num_rows($results)<1){
echo "<center><hr>No products found!<br>Please try again<hr></center>";
}

//mysql_free_result($results);

?>


So basically it has a problem with the SQL query line. I suspect it's a problem passing the variables for some reason, as the query works if I enter the variable values manually.

Any suggestions?
____________________
Past: Honda NSR125R >> Kawasaki ER-5a2 >> Kawasaki ZZR-600e1 >> Suzuki GSXR-750Y >> Honda VTR 1000 Firestorm
"Chickity China the Chinese Chicken..."
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

simon1221
World Chat Champion



Joined: 28 Jul 2004
Karma :
PostPosted: 10:03 - 23 Mar 2006    Post subject: Reply with quote
Try changing it to this:

Quote:
$sqlquery = 'SELECT * FROM '$department' WHERE manufacturer = '$manufacturer'';
$results = mysql_query($sqlquery)
or die("Could not execute query, please try again later: " . mysql_error());


as it sometimes gets picky if you dont have '' on some parts of it.

The mysql_error() will help you to debug it if it still doesnt work, as it gives you the mysql error message, usually like "table 8923789 does not exist".

Thumbs Up
____________________
DNA50 (sorry) ->> TZR125l >> GPZ500S
 Back to top
View user's profile Send private message You must be logged in to rate posts

Suzuki
Roger



Joined: 03 May 2005
Karma :
PostPosted: 10:25 - 23 Mar 2006    Post subject: Reply with quote
Here's your answer:

Code:
$sqlquery = "SELECT * FROM $department WHERE manufacturer = $manufacturer";
$results = mysql_query($sqlquery)
or die("Could not execute query, please try again later");


Basically it's the difference between single and double quotes.

Using single quotes, the string is exactly what you type. Using double quotes, variables are resoved to their values.

So, if $variable = "banana";

Then 'This is a $variable' will be the string This is a $variable.
Whereas "This is a $variable" will be the string This is a banana.

Thumbs Up
____________________
<Simple> no I'm shaven Jon Razz
<Simple> it is a big enough hole.. I'll leave it now
Ride: 1999 Suzuki GSXR600 (yellow/black) IRC: Stats - Relationship Map
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

AcIdBuRnZ
World Chat Champion



Joined: 28 Jul 2003
Karma :
PostPosted: 12:44 - 23 Mar 2006    Post subject: Reply with quote
Thanks for the help guys, but still having a problem. I had tried single and double quotes Confused

If I remove the die line after the query, then these are the errors that return:

Quote:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/hcrief07/public_html/valentines/search_result.php on line 65

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/hcrief07/public_html/valentines/search_result.php on line 84

____________________
Past: Honda NSR125R >> Kawasaki ER-5a2 >> Kawasaki ZZR-600e1 >> Suzuki GSXR-750Y >> Honda VTR 1000 Firestorm
"Chickity China the Chinese Chicken..."
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

Suzuki
Roger



Joined: 03 May 2005
Karma :
PostPosted: 12:45 - 23 Mar 2006    Post subject: Reply with quote
What are the commands on lines 65 and 84 then?
____________________
<Simple> no I'm shaven Jon Razz
<Simple> it is a big enough hole.. I'll leave it now
Ride: 1999 Suzuki GSXR600 (yellow/black) IRC: Stats - Relationship Map
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

AcIdBuRnZ
World Chat Champion



Joined: 28 Jul 2003
Karma :
PostPosted: 12:49 - 23 Mar 2006    Post subject: Reply with quote
Just to add, I have other pages running queries on this database which work fine.

Just can't see where this is failing Mad

Could it be that the form isn't passing the values through Thinking I've checked every line of code, but from experience I know these problems are usually simple in the end!!! Confused

Appreciating the help guys Thumbs Up Smile
____________________
Past: Honda NSR125R >> Kawasaki ER-5a2 >> Kawasaki ZZR-600e1 >> Suzuki GSXR-750Y >> Honda VTR 1000 Firestorm
"Chickity China the Chinese Chicken..."
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

AcIdBuRnZ
World Chat Champion



Joined: 28 Jul 2003
Karma :
PostPosted: 12:51 - 23 Mar 2006    Post subject: Reply with quote
Suzuki wrote:
What are the commands on lines 65 and 84 then?


line 65 wrote:
while ($row = mysql_fetch_array($results)){


line 84 wrote:
if(mysql_num_rows($results)<1){


I know there cannot be a problem with these lines though, as they work fine on other pages!!! Mad
____________________
Past: Honda NSR125R >> Kawasaki ER-5a2 >> Kawasaki ZZR-600e1 >> Suzuki GSXR-750Y >> Honda VTR 1000 Firestorm
"Chickity China the Chinese Chicken..."
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

Suzuki
Roger



Joined: 03 May 2005
Karma :
PostPosted: 13:00 - 23 Mar 2006    Post subject: Reply with quote
You got MSN or something?

I don't have yahoo messenger.
____________________
<Simple> no I'm shaven Jon Razz
<Simple> it is a big enough hole.. I'll leave it now
Ride: 1999 Suzuki GSXR600 (yellow/black) IRC: Stats - Relationship Map
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :
PostPosted: 13:29 - 23 Mar 2006    Post subject: Reply with quote
Hi

I have a suspicion that it may be something like a null field causing problems.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

Suzuki
Roger



Joined: 03 May 2005
Karma :
PostPosted: 13:34 - 23 Mar 2006    Post subject: Re: SQL help needed Reply with quote
You have problems with that massive echo you've got too.

Code:
$row[image]


Should be $row['image'] since that should be an int or a string in the square brackets. The text alone is neither. You need some more quotes! Smile
____________________
<Simple> no I'm shaven Jon Razz
<Simple> it is a big enough hole.. I'll leave it now
Ride: 1999 Suzuki GSXR600 (yellow/black) IRC: Stats - Relationship Map
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

Suzuki
Roger



Joined: 03 May 2005
Karma :
PostPosted: 13:36 - 23 Mar 2006    Post subject: Reply with quote
Try this...

Code:
<?php

$department=$_POST['department'];
$manufacturer=$_POST['manufacturer'];
$name=$_POST['name'];

if (!$manufacturer)
{
echo '<center><hr>You must enter search criteria<br/>'
.'Please go back and try again<hr></center>';
exit;
}

mysql_pconnect('localhost', 'root', '') or die("Unable to connect to database");

mysql_select_db('valentines');
$sqlquery = "SELECT * FROM $department WHERE manufacturer = $manufacturer";
$results = mysql_query($sqlquery)
or die("Could not execute query, please try again later");

while ($row = mysql_fetch_array($results)){
echo '<table width="100%" cellpadding="5" border="0">
<tr>
<td colspan=2 width=100% bgcolor=#990000><font color=#FFFFFF>'.$row['name'].'</font></td></tr>
<tr>
<td rowspan=4><a href='.$row['image'].'><img src='.$row['image'].' height="150" width="150" /></a></td>
<td>Description : '.$row['description'].'</td>
</tr>
<tr>
<td>Colours : '.$row['colours'].'</td>
</tr>
<tr>
<td>Sizes : '.$row['sizes'].'</td>
</tr>
<tr>
<td>Price : '.$row['price'].'</td>
</tr>
</table>';}

if(mysql_num_rows($results)<1){
echo '<center><hr>No products found!<br>Please try again<hr></center>';
}

//mysql_free_result($results);

?>

____________________
<Simple> no I'm shaven Jon Razz
<Simple> it is a big enough hole.. I'll leave it now
Ride: 1999 Suzuki GSXR600 (yellow/black) IRC: Stats - Relationship Map
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

AcIdBuRnZ
World Chat Champion



Joined: 28 Jul 2003
Karma :
PostPosted: 13:57 - 23 Mar 2006    Post subject: Reply with quote
Nope Sad

I have this other site running this PHP, which works fine:

Quote:

<?php

$jobid=$HTTP_POST_VARS['jobID'];

if (!$jobid)
{
echo '<center><hr>You must enter a job number<br/>'
.'Please go back and try again<hr></center>';
exit;
}

mysql_pconnect('localhost', 'root', '') or die("Unable to connect to database");

mysql_select_db('fastcabs');
$sqlquery = "SELECT * FROM jobs WHERE jobID = $jobid";
$results = mysql_query($sqlquery);

while ($row = mysql_fetch_array($results)){
echo "<hr><p>Job ID : " .$row[jobID]. "<p>Driver ID : " .$row[driverID]. "<p>Customer name : " .$row[customer_name]. "<p>Date : "
.$row[pickup_date]. "<p>Time : " .$row[pickup_time]. "<p>Pick up address : " .$row[pickup_address]. "<p>Drop off address : " .$row[dropoff_address];
echo "<hr>";
}

if(mysql_num_rows($results)<1){
echo "<font face=Arial size=+0><center><hr>That job ID does not exist<br>Please try again<hr></center></font><br>";
}


//mysql_free_result($results);

?>



That is practically identical code - but it works!? Confused Cannot see why this one isn't Thinking

If I run the query manually on the database (i.e. inputing the variable values manually), it works fine:

Quote:

SELECT * FROM ladieswear WHERE manufacturer = "Sasso"

____________________
Past: Honda NSR125R >> Kawasaki ER-5a2 >> Kawasaki ZZR-600e1 >> Suzuki GSXR-750Y >> Honda VTR 1000 Firestorm
"Chickity China the Chinese Chicken..."
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

Suzuki
Roger



Joined: 03 May 2005
Karma :
PostPosted: 14:06 - 23 Mar 2006    Post subject: Reply with quote
Something is obviously different. Wink

Have you tried my corrected version yet?
____________________
<Simple> no I'm shaven Jon Razz
<Simple> it is a big enough hole.. I'll leave it now
Ride: 1999 Suzuki GSXR600 (yellow/black) IRC: Stats - Relationship Map
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

AcIdBuRnZ
World Chat Champion



Joined: 28 Jul 2003
Karma :
PostPosted: 14:13 - 23 Mar 2006    Post subject: Reply with quote
OK, I've narrowed it down a bit.

Seems to have a problem with the WHERE command? Confused

This works fine, only when I add in the WHERE part does it have a problem:

Quote:
$sqlquery = "SELECT * FROM $department";


Obviously it is OK with using the variables. Just don't understand it.
____________________
Past: Honda NSR125R >> Kawasaki ER-5a2 >> Kawasaki ZZR-600e1 >> Suzuki GSXR-750Y >> Honda VTR 1000 Firestorm
"Chickity China the Chinese Chicken..."
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

AcIdBuRnZ
World Chat Champion



Joined: 28 Jul 2003
Karma :
PostPosted: 14:14 - 23 Mar 2006    Post subject: Reply with quote
Suzuki wrote:
Have you tried my corrected version yet?


Yes. Exactly the same problem Sad
____________________
Past: Honda NSR125R >> Kawasaki ER-5a2 >> Kawasaki ZZR-600e1 >> Suzuki GSXR-750Y >> Honda VTR 1000 Firestorm
"Chickity China the Chinese Chicken..."
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :
PostPosted: 14:24 - 23 Mar 2006    Post subject: Reply with quote
Hi

Try writing the sql string to the screen and see what it is passing.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

AcIdBuRnZ
World Chat Champion



Joined: 28 Jul 2003
Karma :
PostPosted: 18:32 - 23 Mar 2006    Post subject: Reply with quote
Sorted! Thumbs Up Smile

It will only process the SQL query like this:

Quote:
$sqlquery = "SELECT * FROM $department WHERE `manufacturer` = '$manufacturer' AND `name` = '$name'";


Don't know why it needs the ` `?

Is it dependent on the version of MySQL? Confused
____________________
Past: Honda NSR125R >> Kawasaki ER-5a2 >> Kawasaki ZZR-600e1 >> Suzuki GSXR-750Y >> Honda VTR 1000 Firestorm
"Chickity China the Chinese Chicken..."
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :
PostPosted: 18:52 - 23 Mar 2006    Post subject: Reply with quote
Hi

Wouldn't think so. Very strange syntax. Unless someone set the table up with column names surrounded by inverted commas.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

AcIdBuRnZ
World Chat Champion



Joined: 28 Jul 2003
Karma :
PostPosted: 19:01 - 23 Mar 2006    Post subject: Reply with quote
It was myself that set up the table Confused

Didn't do anything different. Can't suss it out, so will just need to accept that's the way it is going to work Neutral
____________________
Past: Honda NSR125R >> Kawasaki ER-5a2 >> Kawasaki ZZR-600e1 >> Suzuki GSXR-750Y >> Honda VTR 1000 Firestorm
"Chickity China the Chinese Chicken..."
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

AcIdBuRnZ
World Chat Champion



Joined: 28 Jul 2003
Karma :
PostPosted: 19:23 - 23 Mar 2006    Post subject: Reply with quote
What's even stranger, is I decided to remove the `` from the query... and it still works!!!? But it wouldn't earlier? Confused

Meh........
____________________
Past: Honda NSR125R >> Kawasaki ER-5a2 >> Kawasaki ZZR-600e1 >> Suzuki GSXR-750Y >> Honda VTR 1000 Firestorm
"Chickity China the Chinese Chicken..."
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts

simon1221
World Chat Champion



Joined: 28 Jul 2004
Karma :
PostPosted: 20:55 - 23 Mar 2006    Post subject: Reply with quote
Shouldve used the mysql_error function Doh! but oh well you got there in the end.
____________________
DNA50 (sorry) ->> TZR125l >> GPZ500S
 Back to top
View user's profile Send private message You must be logged in to rate posts

Suzuki
Roger



Joined: 03 May 2005
Karma :
PostPosted: 09:59 - 24 Mar 2006    Post subject: Reply with quote
If the names of columns are 'special name', such as "order" or "select" etc, then you need to surround the names in backticks to get the SQL to work.

Sorry, should have noticed that earlier. Embarassed
____________________
<Simple> no I'm shaven Jon Razz
<Simple> it is a big enough hole.. I'll leave it now
Ride: 1999 Suzuki GSXR600 (yellow/black) IRC: Stats - Relationship Map
 Back to top
View user's profile Send private message Send e-mail Visit poster's website You must be logged in to rate posts
Old Thread Alert!

The last post was made 20 years, 166 days ago. Instead of replying here, would creating a new thread be more useful?
  Display posts from previous:   
This page may contain affiliate links, which means we may earn a small commission if a visitor clicks through and makes a purchase. By clicking on an affiliate link, you accept that third-party cookies will be set.

Post new topic   Reply to topic    Bike Chat Forums Index -> The Geek Zone All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum

Read the Terms of Use! - Powered by phpBB © phpBB Group
 
Debug Mode: ON - Server: birks (www) - Page Generation Time: 0.09 Sec - Server Load: 0.32 - MySQL Queries: 13 - Page Size: 137.79 Kb