I am getting an empty result from my pdo

[tunage]

I am trying to query tab delimited first and last names from a file against a table in mysql. I can see my data coming in from the file and test SQL works fine on the mysql command line, but I get zero results from my code.

 

 

1. <?php
2. require_once('auth.php');
3. $conn = new PDO("mysql:hostname=localhost;dbname=$dbname", $username, $pw);
4. $file = fopen('nameList1.txt','r'); //change to whatever name it is
5. $firstname = ''; $lastname = ''; $index = -1; $id = -1;
6. $tab = ' '; //change this to the delimiter, of course
7. while ($line = fgets($file)) {
8. $index = strrpos($line, $tab);
9. $firstname = substr($line, 0, $index);
10. //var_dump($firstname);
11. $lastname = substr($line, $index + strlen($tab));
12. //var_dump($lastname);
13. $stmt = $conn->prepare("SELECT id FROM dbname WHERE FName = $firstname AND LName = $lastname");
14. $stmt->execute();
15. $result = $stmt->fetchAll();
16. var_dump($result);
17. }
18. fclose($file);
19. ?>
20.  
21.  
22.  
23. ******************************************************************
24.  
25. +-----------------------+--------------+------+-----+---------+----------------+
26. | id | mediumint(9) | NO | PRI | NULL | auto_increment |
27. | StateVoterID | int(10) | YES | | NULL | |
28. | CountyVoterID | int(10) | YES | | NULL | |
29. | Title | char(30) | YES | | NULL | |
30. | FName | char(30) | YES | | NULL | |
31. | MName | char(30) | YES | | NULL | |
32. | LName | char(30) | YES | | NULL | |
33. | NameSuffix | char(30) | YES | | NULL | |
34. | Birthdate | int(10) | YES | | NULL | |
35. | Gender | char(30) | YES | | NULL | |
36. | RegStNum | char(30) | YES | | NULL | |
37. | RegStFrac | char(30) | YES | | NULL | |
38. | RegStName | char(30) | YES | | NULL | |
39. | RegStType | char(30) | YES | | NULL | |
40. | RegUnitType | char(30) | YES | | NULL | |
41. | RegStPreDirection | char(30) | YES | | NULL | |
42. | RegStPostDirection | char(30) | YES | | NULL | |
43. | RegUnitNum | int(10) | YES | | NULL | |
44. | RegCity | char(30) | YES | | NULL | |
45. | RegState | char(30) | YES | | NULL | |
46. | RegZipCode | int(10) | YES | | NULL | |
47. | CountyCode | int(10) | YES | | NULL | |
48. | PrecinctCode | char(10) | YES | | NULL | |
49. | PrecinctPart | char(30) | YES | | NULL | |
50. | LegislativeDistrict | char(30) | YES | | NULL | |
51. | CongressionalDistrict | char(30) | YES | | NULL | |
52. | Mail1 | char(30) | YES | | NULL | |
53. | Mail2 | char(30) | YES | | NULL | |
54. | Mail3 | char(30) | YES | | NULL | |
55. | Mail4 | char(30) | YES | | NULL | |
56. | MailCity | char(30) | YES | | NULL | |
57. | MailZip | char(30) | YES | | NULL | |
58. | MailCountry | char(30) | YES | | NULL | |
59. | Registrationdate | char(30) | YES | | NULL | |
60. | AbsenteeType | char(30) | YES | | NULL | |
61. | LastVoted | char(30) | YES | | NULL | |
62. | StatusCode | char(30) | YES | | NULL | |
63. +-----------------------+--------------+------+-----+---------+----------------+
64. 37 rows in set (0.00 sec)
65.  
66.  
67. ********************************** test SQL ***************************************************
68.  
69. mysql> SELECT * FROM list WHERE FName='TARA' AND LName='AABELLERA';
70. +----+--------------+---------------+-------+-------+-------+-----------+------------+-----------+--------+----------+-----------+-----------+-----------+-------------+-------------------+--------------------+------------+------------+----------+------------+------------+--------------+--------------+---------------------+-----------------------+-------+-------+-------+-------+----------+---------+-------------+------------------+--------------+------------+------------+
71. | id | StateVoterID | CountyVoterID | Title | FName | MName | LName | NameSuffix | Birthdate | Gender | RegStNum | RegStFrac | RegStName | RegStType | RegUnitType | RegStPreDirection | RegStPostDirection | RegUnitNum | RegCity | RegState | RegZipCode | CountyCode | PrecinctCode | PrecinctPart | LegislativeDistrict | CongressionalDistrict | Mail1 | Mail2 | Mail3 | Mail4 | MailCity | MailZip | MailCountry | Registrationdate | AbsenteeType | LastVoted | StatusCode |
72. +----+--------------+---------------+-------+-------+-------+-----------+------------+-----------+--------+----------+-----------+-----------+-----------+-------------+-------------------+--------------------+------------+------------+----------+------------+------------+--------------+--------------+---------------------+-----------------------+-------+-------+-------+-------+----------+---------+-------------+------------------+--------------+------------+------------+
73. | 9 | 0 | 458198 | | TARA | L | AABELLERA | | 2 | F | 5804 | | 141ST | PL | | | NE | 0 | MARYSVILLE | WA | 98271 | 0 | 101231 | 0 | 39 | 2 | | | | | | | | 05/05/2004 | P | 11/08/2011 | A |
74. +----+--------------+---------------+-------+-------+-------+-----------+------------+-----------+--------+----------+-----------+-----------+-----------+-------------+-------------------+--------------------+------------+------------+----------+------------+------------+--------------+--------------+---------------------+-----------------------+-------+-------+-------+-------+----------+---------+-------------+------------------+--------------+------------+------------+
75. 1 row in set (27.22 sec)
76.  
77.  
78. *********************** var_dump() *************************************************
79.  
80. array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0) { } array(0)

[tunage]

code and error update   http://bpaste.net/show/200663/

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: no parameters were bound in /var/www/localhost/htdocs/votebd/query2.php on line 15
array(0) { }

[jazzman1]

You need to wrap $firstname and $lastname in your query in single quotes.

Try,

$stmt = $conn->prepare("SELECT id FROM dbname WHERE FName = '$firstname' AND LName = '$lastname'");

[mac_gyver]

in your revised code, you are not binding (or supplying an array in the execute() method call) any variables or values for the two place-holders in the prepared query.

 

also, you should (almost) never run queries inside of loops, even prepared queries only save a minimal amount of time in a loop because you must sill do a round-tirp to the database server to supply the values for the query, and if you were running a prepared query inside a loop, the only query statement logic inside the loop would be the call to the execute() method. by putting the prepare() statement inside the loop, you are re-preparing the query each pass through the loop and the posted code is taking twice as long to run as a non-prepared query would take.

 

since it's not clear what you are attempting to use the result from the query to do, it's not possible to advise on the best method to use to avoid running a query inside of a loop. however, if you are going to insert new data or insert new data/update existing data, you would typically define a unique key and either just use a query like INSERT IGNORE ... or an INSERT ... ON DUPLICATE KEY UPDATE ..., rather than first trying to select data to decide what to do in the code to handle the new/updated values.

 

also, your try/catch block won't work unless you set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION

Edited April 11, 2014 by mac_gyver