homepage Welcome to WebmasterWorld Guest from 23.22.2.150
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Couldn't execute query! What's wrong!?
Spiceydog

5+ Year Member



 
Msg#: 3647080 posted 3:58 am on May 11, 2008 (gmt 0)

It should be fairly obvious what I am trying to do by taking a quick look at the script below. But a quick summary is: I am making a quick search engine that returns this page. You enter a "searchclass" and a "classperiod" and hit submit and it brings you to this page posting the $searchclass and $classperiod. Pretty simple. For some reason when I run it it gives me the or Die field "Couldn't execute query" but nothing seems wrong. Any help?

<?php
$searchclass = $_POST['searchclass'];
$classperiod = $_POST['classperiod'];
include 'login.php';
$query = "SELECT name,
period
FROM schedule
WHERE first='$searchclass'AND period='$classperiod' OR second='$searchclass'AND period='$classperiod' OR third='$searchclass'AND period='$classperiod' OR fourth='$searchclass'AND period='$classperiod' ORfifth='$searchclass'AND period='$classperiod' OR sixth='$searchclass'AND period='$classperiod' OR seventh='$searchclass'AND period='$classperiod' OR eighth='$searchclass'AND period='$classperiod'";
$result = mysql_query($query) or die("Couldn't execute query.");
while ($data = mysql_fetch_array($result)){
{
$name = $data['name'];
}
}
{
?>
<html>
<body>
<?php print "$name"; ?><br />
<?php
}
?>
</body>
</html>

Thanks in advance!

 

wheelie34

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 3647080 posted 8:06 am on May 11, 2008 (gmt 0)

Try echoing out the query so you know it has the correct content, add

echo "$query";

before your $result, you could also try the whole query within phpmyadmin to see if it returns a result

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3647080 posted 8:55 pm on May 11, 2008 (gmt 0)

SELECT name,
period
FROM schedule
WHERE first='$searchclass'AND period='$classperiod' OR second='$searchclass'AND period='$classperiod' OR third='$searchclass'AND period='$classperiod' OR fourth='$searchclass'AND period='$classperiod' ORfifth='$searchclass'AND period='$classperiod' OR sixth='$searchclass'AND period='$classperiod' OR seventh='$searchclass'AND period='$classperiod' OR eighth='$searchclass'AND period='$classperiod';

Look at my bolded quotes, are these typos?

EDIT: It's not this board's software, tested in my reply.

You need a SPACE before each and, and there's one OR in there without one.

Second, you might consider using some brackets. It's very likely that even if it did run, you wouldn't get the results you expect. Is it clear (to mysql) what result you're selecting? Using just the first volley of conditions,

WHERE first='$searchclass' AND period='$classperiod' OR second='$searchclass'

So if $first='$searchclass' and period='$classperiod' it will return a result. But it will also return a result if that is not true and $second='$searchclass'. Or is it . . . . if first matches AND period or second matches?

These two scenarios are clarified by

1. WHERE first='$searchclass' AND (period='$classperiod' OR second='$searchclass')

Both the first field and the bracketed condition MUST match, designated by AND. In the bracketed set, one or the other can match, but it HAS to be one or the other, plus the first, or the entire statement returns false.

2. WHERE (first='$searchclass' AND period='$classperiod') OR second='$searchclass'

Both of the first two conditions MUST match, or just the third.

This can be further complicated if you wish to have some of these nested within others, like,

((a=1 and b=2) or c=3) OR (d=4 and (e=5 or f=6 or g=7) and h=8)

This cascades throughout your select and would probably be better served by a union or join, but in this case - bracket that baby so it's clear and you get what you expect.

Spiceydog

5+ Year Member



 
Msg#: 3647080 posted 8:06 pm on May 12, 2008 (gmt 0)

I am not sure why there weren't spaces in those random places but it didn't look like that on my code on dw. Anyway here is my current script:

<?php
$searchclass = $_POST['searchclass'];
$classperiod = $_POST['classperiod'];
include 'login.php';
$query = "SELECT name,
period
FROM schedule
WHERE ((first='$searchclass' AND period='$classperiod') OR (second='$searchclass' AND period='$classperiod') OR (third='$searchclass' AND period='$classperiod') OR (fourth='$searchclass' AND period='$classperiod') OR (fifth='$searchclass' AND period='$classperiod') OR (sixth='$searchclass' AND period='$classperiod') OR (seventh='$searchclass' AND period='$classperiod') OR (eighth='$searchclass'AND period='$classperiod'))";
$result = mysql_query($query) or die("Couldn't execute query.");
while ($data = mysql_fetch_array($result)){
{
$name = $data['name'];
}
}
{
?>
<html>
<body>
<?php print "$name"; ?><br />
<?php
}
?>
</body>
</html>

So i am still getting the "Couldn't execute query" error even after using your suggestions rocknbil. Also, wheelie: not really sure what that was supposed to do but I did it anyway and of course it just returned "SELECT name, period FROM schedule WHERE (first='us history' AND period='1') OR (second='us history' AND period='1') OR (third='us history' AND period='1') OR (fourth='us history' AND period='1') OR (fifth='us history' AND period='1') OR (sixth='us history' AND period='1') OR (seventh='us history' AND period='1') OR (eighth='us history' AND period='1')"

Duskrider

5+ Year Member



 
Msg#: 3647080 posted 8:16 pm on May 12, 2008 (gmt 0)

Try this:
$result = mysql_query($query) or die("Couldn't execute query because: ".mysql_error());

And see what it tells you.

Spiceydog

5+ Year Member



 
Msg#: 3647080 posted 8:35 pm on May 12, 2008 (gmt 0)

Well now I know that my logic is what is wrong. And that period is not a row on my table. I have two tables, one of them has a schedule that contains a persons name and then first through eighth periods. The other table has the classes that goes in first through eighth periods along with the period of the class. Is there some way that I can query one table for the name of the class and the other table for the period that corresponds with that class?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved