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!
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:
$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.
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.
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 ?
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.
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.