0

For some reason, the query when run through PHP will not return the results. I have tried both queries in the MySQL command line, and they work perfectly there. Here is the code (mysql_connect.php is working perfectly, to clarify).

<?php 
error_reporting(-1);
// retrieve email from cookie 
$email = $_COOKIE['email'];

// connect to mysql database
require('mysql_connect.php');

// get user_id by searching for the email it corresponds to
$id = mysqli_query($dbc,"SELECT user_id FROM users WHERE email=$email")or die('couldn\'t get id');

// get data by using the user_id in $id
$result = mysqli_query($dbc,"SELECT * FROM users WHERE user_id=$id")or die('couldn\'t get data');

//test if the query failed
if($result === FALSE) {
die(mysql_error());
echo("error");
}

// collect the array of results and print the ones required
while($row = mysql_fetch_array($result)) {
echo $row['first_name'];
}
?>

When I run the script, I get the message "could not get id", yet that query works in the MySQL command line and PHPMyAdmin.

Fabian Whitaker
  • 17
  • 1
  • 7
  • Is the cookie actually there? – Fabrizio Mazzoni Apr 23 '14 at 14:46
  • Just to clarify: Are you sure, that mysql_connect.php is working fine? Are you using mysql_connect or mysqli_connect inside your other file? After all, you are using mysql_fetch_array and not mysqli_fetch_array later. So you could have messed up before. – Evan Apr 23 '14 at 14:47
  • Yes, the cookie is there. – Fabian Whitaker Apr 23 '14 at 14:49
  • If you have phpmyadmin, then put the query your query there. If you get a result, then you have no problem. If it return no result, you have a problem with your SELECT. You can also make an echo $id. So you see how you query looks like. – yab86 Apr 23 '14 at 14:50
  • Use MySQLi calls exclusively. MySQL is deprecated and will be removed in the future. You _might_ be getting into trouble by mixing the two libraries. – Phil Perry Apr 23 '14 at 14:53
  • `$id` will not return `id` which you can pass to `$result = mysqli_query($dbc,"SELECT * FROM users WHERE user_id=$id")` – Royal Bg Apr 23 '14 at 14:54
  • When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation to accomplish this because you will probably create severe [SQL injection bugs](http://bobby-tables.com/). – tadman Apr 23 '14 at 15:17

3 Answers3

1

Your query is not valid, you should rewrite it with the following and make sure your you have mysqli_real_escape_string of the $email value before you put it into queries:

SELECT user_id FROM users WHERE email='$email'

Better approach is to rewrite your queries using MySQLi prepared statements:

Here how to get the $id value:

$stmt = mysqli_prepare($dbc, "SELECT user_id FROM users WHERE email = ?");
mysqli_stmt_bind_param($stmt, "s", $email);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id);
mysqli_stmt_fetch($stmt);
Plamen Nikolov
  • 2,643
  • 1
  • 13
  • 24
  • Also worth trying: Using the object-oriented notation. It tends to make this sort of code a lot more readable. – tadman Apr 23 '14 at 15:18
1

Your code won't work for 2 reasons - $id will not magically turn into integer, but a mysqli result. And email is a string so it should be quoted.

But...

Why is all of that?

If you want to fetch all the data for user, for certain email, just make you second query fetch data by email and remove the first one:

SELECT * FROM users WHERE email='$email';

And don't forget to escape your input, because it's in cookie. Or, use prepared statements as suggested.

Royal Bg
  • 6,988
  • 1
  • 18
  • 24
0

You wrote

mysqli_query($dbc,"SELECT user_id FROM users WHERE email=$email");

that is similar to

mysqli_query($dbc,"SELECT user_id FROM users WHERE email=example@example.com");

but it should be

mysqli_query($dbc,"SELECT user_id FROM users WHERE email='example@example.com'");

so you have to do this

mysqli_query($dbc,"SELECT user_id FROM users WHERE email='$email'");

or better

mysqli_query($dbc, 'SELECT user_id FROM users WHERE email=\'' . $email . '\'');

Beside this minor bug You should be aware of SQL injection if someone changes the value of your cookie.

Community
  • 1
  • 1
Jurik
  • 3,244
  • 1
  • 31
  • 52
  • When I do that I get the error "Catchable fatal error: Object of class mysqli_result could not be converted to string" – Fabian Whitaker Apr 23 '14 at 14:48
  • 1
    That is because you use `$id` in your second query. But there you should do something similar to `$result` - `mysql_fetch...`. – Jurik Apr 23 '14 at 14:51