0

I have done this type of SELECT many times, but this time I can't get it to work. Any ideas, please?

$Name = "Dick";

$conn = mysqli_connect($server, $dbname, $dbpw, $dbuser);

$sql = "SELECT id FROM table WHERE $Name = table.first_name";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$customer_id = $row['id'];
Database::disconnect();

echo "customer id = " . $customer_id;
Bricked
  • 115
  • 1
  • 11
  • 1
    @JohnConde: The "backwards" part doesn't matter. – gen_Eric Apr 11 '17 at 20:12
  • Don't forget to [enable exceptions](http://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so errors are made visible. What's very peculiar here is the `Database::disconnect()` call being made, yet you've created a handle independent of that apparently. – tadman Apr 11 '17 at 20:12
  • Any error log you might want to add to your question? – Alfabravo Apr 11 '17 at 20:13
  • 1
    You need to add quotes around $Name – Sloan Thrasher Apr 11 '17 at 20:18
  • It is highly discouraged to use mysqli_* functions since they are highly vulnerable (and deprecated) to SQL injections. Instead it is recommended to use [PDO](http://php.net/manual/pl/book.pdo.php) – yarwest Apr 11 '17 at 20:19
  • @yarwest: That's incorrect. There's nothing wrong with using `mysqli_` functions. You're thinking of the *old* `mysql_` functions (note the lack of the letter `i`). MySQLi is perfectly safe to use, if you use it right. It supports prepared statements just like PDO does. – gen_Eric Apr 12 '17 at 17:49
  • @RocketHazmat if you are right then my knowledge is outdated, I will have to look into that ;) – yarwest Apr 12 '17 at 18:11

3 Answers3

2

If you really DO have a table named table it would be more appropriate to use back ticks around the name since the word TABLE is a reserved word in MySQL. You should also use single quotes around your variable if it contains a string: 

$sql = "SELECT `id` FROM `table` WHERE `first_name` = '$Name'";

Other possible reasons if the query still doesn't work for you:

  1. Make sure you have the connection parameters in the right order. It should be: mysqli_connect($server, $dbuser, $dbpw, $dbname).
  2. You should be using fetch_array() instead of fetch_assoc() if you expect a one row result.
  3. You are mixing PROCEDURAL STYLE with Object Oriented Style when using mysqli_connect() instead of mysqli(), at the same time using $result-> which is object oriented style. You should decide one style and stick with it.

This would be the procedural style of your query:

$Name = "Dick";
$conn = mysqli_connect($server, $dbuser, $dbpw, $dbname); // NOTE THE CHANGED ORDER OF CONNECTION PARAMETERS!
$sql = "SELECT `id` FROM `table` WHERE `first_name` = '$Name'";
$result = mysqli_query($conn, $sql);

$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

$customer_id = $row['id']; // YOUR CUSTOMER ID

mysqli_free_result($result); // FREE RESULT SET
mysqli_close($conn); // CLOSE CONNECTION

And this would be the object oriented style:

$Name = "Dick";
$conn = new mysqli($server, $dbuser, $dbpw, $dbname);
$sql = "SELECT `id` FROM `table` WHERE `first_name` = '$Name'";
$result = $conn->query($sql);

$row = $result->fetch_array(MYSQLI_ASSOC);

$customer_id = $row['id']; // YOUR CUSTOMER ID

$result->free(); // FREE RESULT SET
$conn->close(); // CLOSE CONNECTION

I would recommend naming your table something else than table since it's a reserved word and could get you into parsing problems. The same goes with field names. More reading: https://dev.mysql.com/doc/refman/5.5/en/keywords.html

More about mysqli_fetch_array() and differences in procedural style and object oriented style use: http://php.net/manual/en/mysqli-result.fetch-array.php

Michael Krikorev
  • 2,126
  • 1
  • 18
  • 25
  • 1
    Thank you, all. I missed the obvious. I needed to enclose the '$Name' in single quotes. It works on PDO and mysqli now, but I will stick with PDO for security reasons. Thank you! – Bricked Apr 12 '17 at 12:48
1
$sql = "SELECT id FROM table WHERE '$Name' = table.first_name";
gen_Eric
  • 223,194
  • 41
  • 299
  • 337
Sloan Thrasher
  • 4,953
  • 3
  • 22
  • 40
0

You simply need to concat the variable like this:

$sql = "SELECT id FROM table WHERE " . $Name . " = table.first_name";
gen_Eric
  • 223,194
  • 41
  • 299
  • 337
Alejandro Alfonzo
  • 19
  • 3