SQL problem

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

DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

SQL problem

Post by DJB »

Hi,

I'm having problems getting an sql query to run. The program is meant to compare the data in a form with an underlying MySQL database to ensure that the data in each row is consistent with the corresponding row in the database. However, the query just doesn't execute.

I've declared the query at the start of the program:

Code: Select all

$consistency = ('SELECT a.Module_code, a.Module_title, a.Module_credits, a.New_module,
	   a.Prereq_for, a.Co-req_for, is.Pass_required FROM all_modules a INNER JOIN is_specific is ON
	   a.Module_code = is.Module_code WHERE a.Module_code = \'' . 
                   $_POST["code$i"] . '\' and a.Module_title = \'' .
		   $_POST["title$i"] . '\' and a.Module_credits = \'' .
		   $_POST["credits$i"] . '\' and a.New_module = \'' .
		   $_POST["new$i"] . '\' and a.Prereq_for = \'' .
		   $_POST["pre-requisite$i"] . '\' and a.Co-req_for = \'' .
		   $_POST["co-requisite$i"] . '\' and is.Pass_required = \'' .
		   $_POST["pass$i"] . '\' ');

The following loop is then executed to iterate through each row in the form whenever the user clicks the 'Check Modules' button:

// User pressed button 
if ( $_POST['action'] == 'Check Modules' ) { 
    // Connect to MySQL database 
   $conn = mysql_connect("localhost", "root") or die ( 'Could not connect to the database' ); 
   mysql_select_db("prototype1", $conn) or die( 'Could not locate database on the database server' ); 

   // Verify compulsory modules 
   for ( $i = 0; $i < $MAXCOMPULSORY; $i++ ) { 
       if ( ( $_POST["code$i"] != "" ) ) { 
           // The user entered information in this row
 
	   // execute the sql query
	   $result = mysql_query($consistency, $conn) or die ( 'Query not executed' );
	   // get the number of rows in the result set
	   $number_of_rows = mysql_num_rows($result) or die ( 'num_rows function did not run' );
	   
	   // if the row doesn't exist, create a warning
           if ( $number_of_rows == 0 ) {  
               array_merge($g_aryErrors, array ( 'The following module is not
	       consistent with the database:' . $_POST["code$i"] ) ); 
               $g_numberErrors++; 
           } 
       } 
   }
However, everytime I click the button I just get the 'Query did not execute' warning.

Any suggestions would be greatly appreciated!


feyd | Please review how to post code using

Code: Select all

and

Code: Select all

tags. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
Last edited by DJB on Mon Mar 21, 2005 1:41 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

my powers say: Error in SQL Syntax near 'is ON ...'

instead of dying with 'query not executed'.. die with mysql_error(). Echo out your query too, to make sure the query looks okay before it's sent to mysql.
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

Post by DJB »

Ok, thanks for that. The SQL statement has been edited and now appears to be executing, but the mysql_num_rows function is now failing.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

again, switch the die() to a mysql_error() instead of a simple string.
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

Getting Query Not Executed

Post by DJB »

for the mysql_num_rows function. At this point I'm just looking to see if anything has been returned or not, not what the value is. Can anyone suggest a different function, perhaps fetch_field or something that can be tested and return a boolean type value ?
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

feyd wrote:again, switch the die() to a mysql_error() instead of a simple string.
Have you tried the suggestion ? If yes and you are not getting an error, try running the "echo'd" sql query on the database manually (simply echo the sql statement you are sending to the DB. Copy and paste it) . (You could use phpAdmin or whatever interface you have for MySQL).

mysql_num_rows is the right command command to use.
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

I changed

Post by DJB »

the code to read:-

'die ( 'mysql_num_rows function did not run'. mysql_error());'

as suggested. I still get 'mysql_num_rows function did not run'.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

num_rows() returned zero.
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

Ok

Post by DJB »

I thought that was the problem. I inserted this immediately before the num_rows function and after the retrieval of data:-

echo "Number of rows is ", $result;

which produced this:-

'Number of rows is Resource id #2'

I presume that 'Resource id #2' is a system identifier for this variable.

It then produced the usual num_rows error.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

that just means you got a result of some form from mysql. Nothing more.
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

Yes

Post by DJB »

basically an undefined result because there was nothing to return. So the problem must lie in the logic of the select statement.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it's quite possible the query did in fact return zero rows... :?
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

Unfortunately

Post by DJB »

it should have returned 1 row with the test data. That's why I think the select statement must be wrong.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

so you've run the exact same query in phpMyAdmin? If you haven't, echo out the query string, copy and paste it into a query box in phpMyAdmin..
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

Running the query

Post by DJB »

in 'native' sql, as you suggest, as follows:-

Code: Select all

SELECT all_modules.Module_code, all_modules.Module_title, all_modules.Module_credits,
all_modules.New_module, all_modules.Prereq_for, all_modules.Coreq_for, is_specific.Pass_required
FROM all_modules INNER JOIN is_specific ON all_modules.Module_code = is_specific.Module_code WHERE
all_modules.Module_code = 'COMP2400' 
and all_modules.Module_title = 'Database Principles and Practice' 
and all_modules.Module_credits = 10
and all_modules.New_module = 'No'
and all_modules.Prereq_for = 'COMP3400, COMP3410' 
and all_modules.Coreq_for = '' 
and is_specific.Pass_required = 'No' ;
gets a successful execution. However, it doesn't return a row as it should, soo it must be the logic of the query.
Post Reply