PHP & mySQL Returns

[MackaDee]

Hi, after many years I find myself using PHP and mySQL again, and it seems much has changed, especially with regards to retired functions and new methodologies, and it's driving me a little round the bend trying to figure out what I feel should be basic.

So, to keep things simple, on one page I have an input box. In here, a person can type a value and use the Submit button. This then opens a new page and passes the submitted value.

So, on the second page, I have been taking the submitted value and trying to determine if it's already in the database and display a message to say it's already there or (end goal) to insert it into the table.

For now, I'm just trying to do a simple 'Yes, it's new/Sorry, have it already' message.

I've tried both SELECT COUNT(*) FROM myTable WHERE myField = "$myVar"' as well as the SQL in the example below.

Ultimately, I just want to run something like the above, check if the COUNT(*) is 0 or more, and return my message.

<?php

$myVar = htmlspecialchars($_GET["example"]);

   $sql = 'SELECT * FROM myTable WHERE myField = "$myVar"';
    
  $res = $conn->query($sql);
  $count = $res->fetchColumn();

 if ($count == 0)
    echo 'That is a new one.';
else
    echo 'That is already stored.';

?>

For the record the database connection is working fine and is included elsewhere and, I feel, likely not relevant here other than the appropriate terminology.

[Barand]

You were right with the COUNT(*).

You should be using a prepared statement and not trying to sanitize the input using htmlspecialchars (which is an output function)

$sql = 'SELECT COUNT(*) FROM myTable WHERE myField = ?';

$stmt = $con->prepare($sql);
$stmt->execute( [ $_GET["example"] ] );

if ($stmt->fetchColumn() == 0 {

 

[MackaDee]

Thanks for replying, @Barand

when I do that, I get the following where Line 17 is $res->execute( [ $_GET["example"] ] );

Quote

 

[19-Nov-2022 14:20:55 America/Boise] PHP Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /my/site/path/my_page.php:17

Stack trace:

#0 /my/site/path/my_page.php(17): PDOStatement->execute(Array)

#1 {main}

thrown in/my/site/path/my_page.php on line 17

 

Code is:

  $myVar = $_GET["example"]);
  
   $sql = 'SELECT Count(*) FROM myTable WHERE myField = "$myVar"';
 
  $res = $conn->prepare($sql);
  $res->execute( [ $_GET["example"] ] );
  
  if ($res->fetchColumn() == 0)
    echo 'That is a new one.';
  else
    echo 'That is already stored.';

Ultimate goal is to pass the parameter to a new page, check if it's in the database and then insert it into the database if not, or display an informative message if so.

[Barand]

The whole point of prepared statements is not to put variables in the query.

Look at my code again.

[mac_gyver]

your database design must enforce uniqueness, it is the last step in the process. when there are multiple concurrent instances of your script running, they can all query to find that the value does not exist and attempt to insert it. the first one will win this timing race and succeed. all the following ones must fail with an error. to do this, the column you are trying to match must be defined as a unique index. you would then just simply attempt to insert the data and in the error handling, which should be using exceptions, you would test if the error number is for a unique index violation (1062), and setup a message letting the user know that the value is already in use. for all other error numbers, just rethrow the exception and let php handle it.

[Steveinid]

try this code:

$myVar = htmlspecialchars($_GET["example"]);

   $sql = 'SELECT COUNT(id) AS total FROM myTable WHERE myField = :myvar;';
  
        $arguments = ['myvar' => $myVar];
        if (!$arguments) {
                return $conn->query($sql);
            }
        $statement = $conn->prepare($sql);
        $statement->execute($arguments);
        $row = $statement->fetch(); 
        $total = $row['total'];
        if ($total > 0) {
         echo 'That is already stored.';
        } else {
//           INSERT $myVar into the database
        }

Quote

Quote