Page 1 of 2

SQL problem

Posted: Sun Mar 20, 2005 9:47 am
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]

Posted: Sun Mar 20, 2005 9:53 am
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.

Posted: Sun Mar 20, 2005 1:14 pm
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.

Posted: Sun Mar 20, 2005 5:02 pm
by feyd
again, switch the die() to a mysql_error() instead of a simple string.

Getting Query Not Executed

Posted: Mon Mar 21, 2005 7:15 am
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 ?

Posted: Mon Mar 21, 2005 7:40 am
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.

I changed

Posted: Mon Mar 21, 2005 10:36 am
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'.

Posted: Mon Mar 21, 2005 10:42 am
by feyd
num_rows() returned zero.

Ok

Posted: Mon Mar 21, 2005 10:47 am
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.

Posted: Mon Mar 21, 2005 10:56 am
by feyd
that just means you got a result of some form from mysql. Nothing more.

Yes

Posted: Mon Mar 21, 2005 10:59 am
by DJB
basically an undefined result because there was nothing to return. So the problem must lie in the logic of the select statement.

Posted: Mon Mar 21, 2005 11:06 am
by feyd
it's quite possible the query did in fact return zero rows... :?

Unfortunately

Posted: Mon Mar 21, 2005 11:11 am
by DJB
it should have returned 1 row with the test data. That's why I think the select statement must be wrong.

Posted: Mon Mar 21, 2005 11:31 am
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..

Running the query

Posted: Mon Mar 21, 2005 12:24 pm
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.