Can't get code to work with prepared statements.

[dennisgreenwood48]

The first piece of code, "screenshot_1", works perfectly but of course is open to SQL injection. If I use prepared statements as per "screenshot_2", and change $result from $con->query($query) to $result= $query->execute()it doesn't work and gives the error, "Fatal error: Uncaught Error: Call to a member function fetch() on bool ". I have read somewhere that this is caused because the 'Select' statement has failed.I am amazed that three lines of code (copied from a php manual) has caused the whole issue to fail. Where have I gone wrong?

,  

[Barand]

You say you get the error on the fetch, but that is the bit of the code you decided not to show us!

[mac_gyver]

2 hours ago, dennisgreenwood48 said:

$result= $query->execute()

the value returned from the ->execute() method is -

Quote

 

Return Values

Returns TRUE on success or FALSE on failure.

 

you don't fetch data using that returned value.

->query() and ->prepare() are PDO methods. they both return a PDOStatement object (when successful.) ->fetch() and ->execute() are PDOStatement methods. ->fetch() returns a row of data (or a false if there's no data to fetch) and ->execute() returns a boolean value as listed above.

typical code for a non-prepared query -

$sql = "build the sql query statement in a php variable";
$stmt = $pdo->query($sql);

// fetch a single row of data
$row = $stmt->fetch();

// fetch all the rows of data
$rows = $stmt->fetchAll();

typical code for a prepared query -

$sql = "build the sql query statement in a php variable. ... WHERE founder LIKE ? ...";
$stmt = $pdo->prepare($sql);
// provide the corresponding input(s) as an array to the execute call -
$stmt->execute(["%$keyword%"]);

// fetch a single row of data
$row = $stmt->fetch();

// fetch all the rows of data
$rows = $stmt->fetchAll();

the only lines that change are the ->query() v.s. ->prepare()/->execute()

next, don't run a query multiple times just to get the column names. either use the PDOStatement ->getColumnMeta() method, or more universally, fetch all the data from the query into an appropriately named php variable (see the ->fetchAll() method), then reference the zeroth row [0] of that fetched data to access the column names.

lastly, when you make the PDO connection, set the default fetch mode to assoc, so that you don't need to specify it in each fetch statement. when you make the connection, you should also set the error mode to exceptions and set emulated prepared queries to false.

[dennisgreenwood48]

Have tried the 'typical code for a prepared query' and while it shows no errors, all I get is a blank page. Thinking I needed some means of displaying the results I  added an extra line, 'print_r ($rows)' but all this does is to display 'Array()' in the top left hand corner of the page and not the results of the 'SELECT' statement. I want the results to display in a table as the original code does. I don't know enough about php to make great changes in the code. The last time I did anything like this was over 15 years ago.  

[kicken]

If you did

$rows = $stmt->fetchAll();
print_r($rows);

and just see Array() on the page that would indicate your query returned zero results.   Make sure your query should return results if you manually run it with the same keyword value.

If you continue having trouble, show us your updated code, including your attempt to fetch and display the results.  Preferable as text and not a screenshot.  Use the <> button on the editor toolbar and paste your code into the dialog that opens up.