Jump to content

PHP & mySQL Returns


MackaDee
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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 {

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

 

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.