Query different MySQL tables from PHP

Question

been googling for hours and I'm quite new to this.

I have two identical tables in one MySQL database:

One named "users" and one named "keys".

They are identical for testing purposes.

When I query "users" I get a response, when I query "keys" I get nothing.

Querying users I get the expected response:

<?php

require('../db/connect.php');

$query = mysql_query("

    SELECT  name
    FROM    users
    WHERE   can_share = '".$_POST['URLkey']."'

");

echo mysql_result($query, 0);

?>

Querying keys I get nothing:

<?php

require('../db/connect.php');

$query = mysql_query("

    SELECT  name
    FROM    keys
    WHERE   can_share = '".$_POST['URLkey']."'

");

echo mysql_result($query, 0);

?>

I guess there must be some basic understanding of databases that has slipped by me, but still, after hours of searching I can't figure it out. Maybe I'm becoming retarded.

You are vulnerable to [SQL injection attacks](http://bobby-tables.com), and never bother checking if your queries worked at all. Never assume success. Always assume failure and treat success as a pleasant surprise. — Marc B, Jul 08 '14 at 21:57
are those 2 different files or 1 file that contains both pieces of the code? — GGio, Jul 08 '14 at 21:59
Can you post a screenshot of the schema of both tables, or anything like that? — djbhindi, Jul 08 '14 at 22:00
@djbhindi: both look like this: https://www.dropbox.com/s/8jxqez1x44sv4av/Screenshot%202014-07-09%2000.02.04.png — Jonas Barsten, Jul 08 '14 at 22:04
@Jonas Barsten Have you made sure that $_POST['URLkey'] actually has data in it or is set? Have you tired echoing $query to see what full query looks like? — Mr. Concolato, Jul 08 '14 at 22:10
@Mr.Concolato: yes, I've tried to echo back $_POST['URLkey'] and it returns back the same that I sent. — Jonas Barsten, Jul 08 '14 at 22:12
For debugging purposes I'd recommend enabling the PHP error reporting (Have a look here: http://stackoverflow.com/a/6575502/3293843)... and MySQL errors (please consider though, what Marc B is saying and instead of the mysql_* library have a look at PDO or mysqli_*) by adding `or die(mysql_error())` to your query — faerin, Jul 08 '14 at 22:13
@entiendoNull: thanks to the debugging I got this: "mysql_result() expects parameter 1 to be resource, boolean given in ..." and figured out that, for some reason, I had to use some of these: "`" — Jonas Barsten, Jul 08 '14 at 22:31
@entiendoNull: yup, writing an answer now :) ... But I still find it strange that only one of the tables needed that formatting. — Jonas Barsten, Jul 08 '14 at 22:33

score 0 · Accepted Answer · answered Jul 08 '14 at 22:54

I think that might be due to table name being 'keys'.

Have a look here: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

You have to understand while designing your tables and naming the attributes that some words are reserved by MySQL itself.

So, if you name your table 'WHERE' you will have troubles in usual query. Why?

'SELECT * FROM WHERE'

Such a query obviously doesn't work, as it will ask you to provide table name.

Now, when you change format situation also changes:

'SELECT * FROM `WHERE`'

As you can see I added some backwards commas. In MySQL they are used to denote names of tables or fields. If you use them - the server processes and reads your query correctly.

So, that's why your edited query worked fine in the end.

If you feel strongly about table being named 'keys' - add suffix or prefix - like 'my_keys' or 'keys_table'. — Anatoliy Kim, Jul 08 '14 at 22:55
Ah, of course! Thank you very much! When I get enough rep. I'll vote you up ;) — Jonas Barsten, Jul 08 '14 at 23:10

score -1 · Answer 2 · edited May 23 '17 at 10:26

Thanks to enabeling debugging, I got this message:

mysql_result() expects parameter 1 to be resource, boolean given in ...

And figured out that I had to query "keys" like this:

<?php

require('../db/connect.php');

$query = mysql_query("

    SELECT  `name`
    FROM    `keys`
    WHERE   `can_share` = '".$_POST['URLkey']."'

");

echo mysql_result($query, 0);

?>

Now it works, but I still don't understand why only one of the tables needed that formatting. And I have learned that I should rewrite the whole thing to not be vulnerable to SQL injection attacks. ...

EDIT: It seems like the words "key" and "keys" and some more are reserved by MySQL, so to use them, they have to be formatted like that.

Query different MySQL tables from PHP

2 Answers2