Ok, i have a pretty simple PHP script running. The problem is, MySQL says that I have an error in syntax, but I can't figure out what they could be!

Here is the two lines of code that matter
$mysql = "SELECT 'power' FROM 'Attributes' WHERE userid = '" . $userid . "'";
$power = mysql_query($mysql) or die("Query failed : " . mysql_error());

And the error I am getting is:
Query failed : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''Attributes' WHERE userid = '1'' at line 1
I don't understand what the error is or why it says its in line 1, when its really on like line 13.
Thanks for any help.

seems you just have a problem with quotes

just try

if userid declared as integer @ mysqldb

$mysql = "SELECT power FROM Attributes WHERE userid = $userid"

and if you decalred userid as varchar field try

$mysql = "SELECT power FROM Attributes WHERE userid = '$userid'"

The field name (power) shouldn't have quotes round it. Your query will just return the string 'power'.

Toby

Like JimPanse says - no quotes round the table name either :-)

You may have seen backticks (``) used round the table name - that's legal.

Toby

ok it worked. Thanks guys. Now i have a new problem.
$power is returning as Resource id #3 instead of returning the value of that field. What's going on?

i never use backticks at table or fieldname values just for the whereclausel:)

"select B.fieldA, R.filedB from blah B, roelps R where R.filedC = 'QuoteIfAlpha' && B.fieldC = NoQuoteIfNUm Limit 1"

try this:

$mysql = "SELECT 'power' FROM 'Attributes' WHERE userid = '" . $userid . "'";
$power = mysql_query($mysql) or die("Query failed : " . mysql_error());
$power = mysql_result($power, 0);

mysql_query return a recordset of your selected datas

you have to fetch em into an array

if you have more then one records in your recordset do:

while ($datas = mysql_fetch_array($power)){
print_r($datas);
}

or if you know, that you just get one record back, you can do

$userdata = mysql_fetch_array($power);
print_r($userdata);

it works! Thanks so much guys. I wrote a really long script too with the probably the same errors as this one, that one would have been a doozy! thanks!

ok now i have another question. If i want to test to make sure an entry is not already in the database, how would I do that?
$sql = "SELECT loginname FROM Players";
$rs1 = mysql_query($sql);

Is all I have right now. If i have an unknown number of rows, how do I test every single one?

just do somethin like:

$query = "SELECT loginname FROM Players WHERE id = '$id'";
$there = @mysql_query($query) or die("Error : Please inform the admin. " . mysql_error());
$d = mysql_num_rows($there);
if (!$d) {
//nothing returned, its not there
} else {
//something return, so there is one
}

just replace the where clause with the appropriate info

That doesn't work for two reasons. The first one is, I want to test whether the name exists at all, so I don't have any other field to discern by. Secondly, it reports an error instead or not returning. Thanks though.
Would this work?
$y = 0;
$sql = "SELECT loginname FROM Players";
$rs1 = mysqlquery($sql) or die("Error: Please inform a moderator. " . mysql_error());
$numrows = mysql_num_rows($rs1);
for ($i=0; $i<$numrows; $i++; )
{
mysql_result($rs1, $y);
$y++;
}

umm, well will this work?

$query = "SELECT * FROM Players WHERE loginname = '$loginname'";
$there = @mysql_query($query) or die("Error : Please inform a moderator. " . mysql_error());
$d = mysql_num_rows($there);
if (!$d) {
//nothing returned, its not there
echo "Congratulations the name is not there.";
} else {
//something return, so there is one
die("Error: Name exists!");
}

you need to specify a where clause in order for it to search anything, otherwise it will return all the records.

it gives the same error of Unknown column 'username' in 'where clause'. This is stumping me.

is there a column 'username' in the table? check. if not then you probably mispelled something or entered in the wrong column name

no, 'username' is the name entered to check. it is $loginname. so basically its saying unknown column $loginname in 'where clause'

ok nevermind about that anymore, I was just quoting incorrectly :blush: . Now I have a new problem :eek: . For every function that calls a result resource it says supplied argument is not a valid MySQL result resource, even though it is.

*sigh*

You need to read a PHP and MySQL tutorial.

do you have any good ones to suggest? I would if i could find a good one.

OK, after reading the MySQL manual, I have every error resolved except this one: Warning: mysql_result(): Unable to jump to row 1 on MySQL result index 4 in /home/nurowars/public_html/test/register.php on line 51

It creates the row but the number it inserts is wrong. The code is here


$last = mysql_num_rows($rs5);
$userid = mysql_result($rs5, $last);

What does it mean unable to jump row?

$last = mysql_num_rows($rs5);
$userid = mysql_result($rs5, $last);


It's a bit confusing here. You've done a query, and you want to know how many rows you have. That goes into $last.

If you have one row, then $last will be 1.

However, the index for mysql_result starts from 0, so by putting the value of $last in there, you're asking for the second row, when we know there's only one!

You could put:

$userid = mysql_result($rs5, $last-1);

but I can't help thinking that the whole thing is a bit confused. Are you doing a select, and expecting to return a single row? I would check if mysql_num_rows() returned a single row, then do:

$userid = mysql_result($rs5, 0);

But maybe I'm just missing what you're trying to do :-)

Toby

i am trying to use it to make ids. So it queries the table and says whats the last id you have, adds 1 to it and inserts in a new row. The minus 1 thing worked perfectly. Everything is working great, except my database. On every table every time i insert a new row, they don't want to stay there! They change position. I'm not sure why. I figured out a way to get around this, but I'd still like to know why this happens.

thanks everyone for your help!
:D

There's a basic theoretical principle of relational databases, that you can infer absolutely nothing from the order of the rows you get from a query, unless you specifically order them.

In other words, if you select all rows from a table, you can not assume that the most recently added entry will be the last row returned. If you are assigning autoincrement ID numbers, you can order by ID. If you don't have any suitable field to order by, add a timestamp field. You can just leave that blank, and when the row is created, it will have the current date and time added automatically.

Toby