Jump to content


Photo

sql num_rows query - help!


  • Please log in to reply
11 replies to this topic

#1 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 20 March 2006 - 05:56 PM

////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')");
}

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?!

#2 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 20 March 2006 - 06:01 PM

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'"


Info: PHP Manual


#3 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 20 March 2006 - 06:10 PM

good spot but i still get the error!!

#4 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 20 March 2006 - 06:30 PM

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

Info: PHP Manual


#5 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 20 March 2006 - 06:49 PM

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

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

with this 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>");
}

////////////

the <pre> outputs:

1

and thats it! any ideas?

#6 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 20 March 2006 - 06:55 PM

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:

$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);
}

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

Info: PHP Manual


#7 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 20 March 2006 - 07:03 PM

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:
<?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());
}
?>

Ken

#8 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 20 March 2006 - 07:13 PM

okay, ive used the improved code.

heres what i get when i input some dummy values

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')

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

#9 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 20 March 2006 - 07:24 PM

Tablenames cannot have spaces, unless I'm mistaken!

Info: PHP Manual


#10 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 21 March 2006 - 05:38 PM

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

#11 txmedic03

txmedic03
  • Members
  • PipPipPip
  • Advanced Member
  • 313 posts
  • LocationCall, TX, USA

Posted 22 March 2006 - 09:37 AM

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...

SEMPER FIDELIS!

I can't stop you from doing something silly, but at least I can help you do it right.


#12 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 22 March 2006 - 12:48 PM

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

Ken




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users