sql num_rows query - help!

[willwill100]

[code]
////if their name does not already exist in the relevant table, insert it////
$namesthere = mysql_fetch_array(mysql_query("SELCT * FROM `$compl` WHERE `Name`=$name"));
if (mysql_num_rows($namesthere)==0){
$query = mysql_query("INSERT INTO '$compl' ( `Name` , `Sail Number` , `Class` , `Score` ) VALUES ( '$name', '$snum', '$boattype', '$score')");
} [/code]

The code above is giving the "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource" error. Can anyone give me a hand please?!

[ober]

It's probably because: "SELCT * FROM `$compl` WHERE `Name`=$name"

1) you spelled "SELECT" wrong.
2) $name should be in quotes: "SELECT * FROM `$compl` WHERE `Name`= '$name'"

[willwill100]

good spot but i still get the error!!

[ober]

Post the code again with the changes and double check to make sure your tablenames and column names match what you actually have.

[willwill100]

i'm sure ive got the table name correct but i now only get one error (before I got 2) saying:

[code]Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Documents and Settings\Will\My Documents\xampp\htdocs\sailing\rescalc.php on line 91[/code]

with this code:

[code]////if their name does not already exist in the relevant table, insert it////
$namesthere = mysql_fetch_array(mysql_query("SELECT * FROM `$compl` WHERE `Name`='$name'"));
if (mysql_num_rows($namesthere)==0){
$query = mysql_query("INSERT INTO `$compl` ( `Name` , `Sail Number` , `Class` , `Score` ) VALUES ( '$name', '$snum', '$boattype', '$score')");
echo ("<pre>" . $query . "</pre>");
}

////////////
[/code]

the <pre> outputs:

[code]1[/code]

and thats it! any ideas?

[ober]

That's because mysql_query is going to return the result set number.

You really need to stop nesting your functions so you can troubleshoot more easily:

[code]$query = "INSERT INTO `$compl` ( `Name` , `Sail Number` , `Class` , `Score` ) VALUES ( '$name', '$snum', '$boattype', '$score')";
$result = mysql_query($query) or die(mysql_error());
if($result && mysql_num_rows($result) > 0)
{
   $row = mysql_fetch_array($result);
}
[/code]

That's how it should be structured. Then you can echo the query quite easily.

[kenrbnsn]

You have too many compound statement to really determine what is causing the problem. Let's break them down to the individual statements with error checking along the way:
[code]<?php
////if their name does not already exist in the relevant table, insert it////
$q = "SELECT * FROM `$compl` WHERE `Name`='$name'";
$rs = mysql_query($q) or die('Problem with query: ' . $q . '<br>' . mysql_error());
$namesthere = mysql_fetch_assoc($rs);
if (mysql_num_rows($namesthere)==0){
    $q = "INSERT INTO `$compl` ( `Name` , `Sail Number` , `Class` , `Score` ) VALUES ( '$name', '$snum', '$boattype', '$score')";
    echo ("<pre>" . $q . "</pre>");
    $rs = mysql_query($q) or die('Problem with insert query: ' . $q . '<br>' . mysql_error());
}
?>[/code]

Ken

[willwill100]

okay, ive used the improved code.

heres what i get when i input some dummy values

[code]Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Documents and Settings\Will\My Documents\xampp\htdocs\sailing\rescalc.php on line 93

INSERT INTO `topper main points` ( `Name` , `Sail Number` , `Class` , `Score` ) VALUES ( 'Will Williams', '45052', 'Topper', '129')[/code]

i put the sql into phpmyadmin and it worked so what could be the problem?

[ober]

Tablenames cannot have spaces, unless I'm mistaken!

[willwill100]

sorry took so long to reply, table names can have spaces (it has worked for the rest of my project) what could the problem be? is the only problem my database or what?

thanx

[txmedic03]

if mysql_query() did not return anything then mysql_fetch_assoc() will fail. There is nothing there for it to fetch. You do no get num_rows from fetch_assoc...you get an associative array of one row from mysql_fetch_assoc() based on the query. Take the query and do mysql_num_rows() on it to get the number of rows or a SELECT count(*) with conditions...

[kenrbnsn]

You need to do the mysql_num_rows() function after the mysql_query()
[code]<?php
$rs = mysql_query($q) or die('Problem with query: ' . $q . '<br>' . mysql_error());
if (mysql_num_rows($rs)==0){
?>[/code]

Ken