Page 1 of 1
code not working
Posted: Sat Mar 31, 2012 1:48 pm
by swapna10
I am using php/mysql for my project.
I have ONE problem here. My mysql query is not working ..I don't know why? Here is that line -
Code: Select all
$query =mysql_query("SELECT Course_ID FROM Course where Student_ID='$Stud_ID' AND Course_Name='COSC 3312' AND Course_Name='COSC 2430'");
$count=mysql_num_rows($query);echo $count;
If I try to echo count, it will give me 0. It is supposed to give me 1. That is how it is not working.
If anyone knows, let me know....
Re: code not working
Posted: Sat Mar 31, 2012 2:22 pm
by Celauran
Have you run the query manually to confirm that it works? One course having two names seems a little odd.
Re: code not working
Posted: Sat Mar 31, 2012 3:17 pm
by phphelpme
I can see nothing wrong with your SQL Query so I can only assume that the query conditions have not been met which would result in the zero count.
I suggest you check that the student id does indeed appear within those two courses or indeed as Calauran stated the two courses does seem a little odd although it can clearly be a valid query.
Also, check that your table and column names are correct. The above query must have all conditions met before a result of more than 0 can be found. So ID, and both courses MUST match no matter what.
Best wishes
Re: code not working
Posted: Sat Mar 31, 2012 8:44 pm
by califdon
Umm, not really. Course_Name is a column in the table Course and it can have only one value for any one row, so no row can meet the criteria of the query.
Re: code not working
Posted: Tue Apr 10, 2012 9:24 am
by php_rockzz
Its possible if Student id is not unique.. In that case it might b possible if one student goes for two courses..

Re: code not working
Posted: Tue Apr 10, 2012 11:16 am
by califdon
php_rockzz wrote:Its possible if Student id is not unique.. In that case it might b possible if one student goes for two courses..

No, every row that is returned must meet the criteria of the Where clause. That is the purpose of the Where clause. It is never valid to specify that the same column be equal to more than one value.
Re: code not working
Posted: Tue Apr 10, 2012 11:56 am
by phphelpme
I agree with Califdon but you could have a situation where the %xxx% is used which would allow for two values to be present or not. It all depends on how the information is saved within the database field to begin with.
The above code that has been presented must have those WHERE statements met for anything to be returned just as Califdon has stated.
Best wishes
Re: code not working
Posted: Tue Apr 10, 2012 12:03 pm
by Celauran
phphelpme wrote:I agree with Califdon but you could have a situation where the %xxx% is used which would allow for two values to be present or not. It all depends on how the information is saved within the database field to begin with.
The above code that has been presented must have those WHERE statements met for anything to be returned just as Califdon has stated.
Best wishes
No. Califdon is absolutely right and I'm surprised I missed it earlier. Any given column can only have a single value. This query requires it have two. Not possible. You'll get an empty set every time.
Re: code not working
Posted: Tue Apr 10, 2012 12:21 pm
by phphelpme
Celauran, explain what you mean because I was under the impression that by using the % % you are searching for a certain piece of data within a a field so that would allow for two possible answers as the field could contain both appearing data.
Best wishes
Re: code not working
Posted: Tue Apr 10, 2012 12:41 pm
by Celauran
You're right. Apparently this thread makes me unable to read. I was looking at the OP's query with two specific equal clauses.
Re: code not working
Posted: Tue Apr 10, 2012 12:44 pm
by phphelpme
haha, thanks for getting back to me. I was confused about the whole thing then... lol
Best wishes
Re: code not working
Posted: Tue Apr 10, 2012 1:18 pm
by califdon
The point here is that = (equals) is not the same operator as LIKE, using %. You can have a WHERE clause with the same column < (less than) some value AND > (greater than) another value, but never = 2 values. If you used LIKE and % with 2 values, such as: `name` LIKE '%abc%' AND `name` LIKE '%xyz%', it would only return rows where the field name contained a string that included BOTH 'abc' AND 'xyz', so it would return a row that contained 'mnoxyzdefabc' but would not return a row that contained 'abcdef'.
Re: code not working
Posted: Tue Apr 10, 2012 2:26 pm
by phphelpme
Absolutely spot on califdon. I could not agree more. I was just trying to think outside the box as looking at the code above it would appear that these two values must be matched as required by the programmer. So a way of doing this depending on how the data is stored would be the use of such 'LIKE' statements.
However, the above statement as it stands is exactly how you have stated it califdon. It MUST meet the WHERE statements so as it stands this could never happen with the current code.
It is hard because swapna10 has not got back to anyone regards this, so we are just guessing as to what he actually wants to achieve here or has this just been a mistake in writing the code etc.
Thanks for clarifying things for us.
Best wishes
Re: code not working
Posted: Tue Apr 10, 2012 6:42 pm
by califdon
The grammar of SQL (and computer logic, generally) uses some of the same words as English grammar, but that can be quite misleading. We could think of it as, "I want to return rows with Course_Name='COSC 3312' AND rows with Course_Name='COSC 2430'", but that would be just wrong because of the way the computer language uses the word "AND". Here it means rows with (Course_Name='COSC 3312' AND Course_Name='COSC 2430')" and that is impossible, so the query will ALWAYS return no records.