Query DB if record exist and echo answer if not

Question

I have db with list of domains and their owners. What I want to do is query to check if domain exist in DB and display it name and owner, or if domain does not exist display "this domain does not exist" and button linking to external URL. So far I can query and display when domain exist and display nothing if not exist. I can't display "thist domain does not exist". I tried other topics on stack but not work for me. Here is my Code

   <?php
   $con= new mysqli("localhost","root","","sprawdzarka");
   $set = !empty($_POST['search']) ? $_POST['search'] : (!empty($_GET['search']) ? $_GET['search'] : null);
   if ($set) {
   $show = "SELECT * FROM domeny WHERE domena = '$set'";
   $result = mysqli_query($con, $show);
   while ($row=mysqli_fetch_array($result)){ ?>
   <tr>
               <td>Domain:<?php echo $row['domena']; ?></td>
               <td>Belongs to:<?php echo $row['handlowiec']; ?></td>
  </tr>
  <?php } ?>
  <?php } ?>

Where have you tried outputting `this domain does not exist`? — user3783243, Dec 24 '20 at 22:35
You are open to SQL injections, parameterize and use prepared statement. Your `$_POST` and `$_GET` can be replaced with `$_REQUEST`. — user3783243, Dec 24 '20 at 22:36
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Dec 24 '20 at 23:04
If you are only starting to learn PHP then you should learn PDO instead of mysqli. PDO is much easier and more suitable for beginners. Start here https://phpdelusions.net/pdo — Dharman, Dec 24 '20 at 23:07

score 4 · Answer 1 · answered Dec 24 '20 at 23:24

You should not mix application logic and presentation logic. Keep HTML and PHP separate.

To check if you received any results from the database the easiest way is to fetch any results into an array and check if it is empty.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$con = new mysqli("localhost", "root", "", "sprawdzarka");
$con->set_charset('utf8mb4'); // always set the charset
$set = $_REQUEST['search'] ?? null;
if ($set) {
    $stmt = $con->prepare('SELECT * FROM domeny WHERE domena = ?');
    $stmt->bind_param('s', $set);
    $stmt->execute();
    // fetch all into an array
    $result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

    // If you have non-empty array then display records
    if ($result) {
        foreach ($result as $row):
        ?>
        <tr>
                <td>Domain:<?= $row['domena']; ?></td>
                <td>Belongs to:<?= $row['handlowiec']; ?></td>
        </tr>
        <?php
        endforeach;
    } else {
        echo 'thist domain does not exist';
    }
}

Steven · Answer 2 · 2020-12-24T23:37:36.967

Problems

You need to use an if/else statement to check if rows were returned from the query
- If rows were returned then output the row
- If not then output notification to that effect
You should be using prepared statements to protect your database from malicious user input
You don't need to use a while loop to access the returned results
- Unless you're expecting multiple rows returned -- which I doubt

Solution(s)

Notes

I've adjusted your use of ternary logic and incorporated the NULL coalescent operator instead:

$search = $_POST["search"] ?? $_GET["search"]  ?? NULL;

// if     $_POST["search"] has data then set $search = $_POST["search"]
// elseif $_GET["search"]  has data then set $search = $_GET["search"]
// else                                  set $search = NULL

mysqli

// Set database variables
$db_host = "localhost";
$db_user = "root";
$db_pass = "some+password";
$db_name = "sprawdzarka";

// Enable error reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Connect to the database
$mysqli = new mysqli($db_host, $db_user, $db_pass, $db_name);

// Set the search parameter to value from $_POST, $_GET, or NULL
$search = ( $_POST["search"] ?? NULL ) ?: ( $_GET["search"]  ?? NULL );

// Check to see if the search URL was submitted
if ( $search ){
    $sql   = "SELECT * FROM domeny WHERE domena = ?";  // Prepare query string
    $query = $mysqli->prepare($sql);                   // Prep the query
    $query->bind_param("s", $search);                  // Bind the URL
    $query->execute();                                 // Execute the query
    $query->store_result();                            // Store the returned records
    $query->bind_result($domena, $handlowiec);         // Bind returned columns to variables

    // Check to see if a result was returned
    if( $query->fetch() ){
        // Print out the table row
        echo "
            <tr>
                <td>Domain:     {$domena}    </td>
                <td>Belongs to: {$handlowiec}</td>
            </tr>
        ";
    }
    else{
        // Print out if no results were returned
        echo "This domain doesn't exist: <a href=\"{$search}\">{$search}</a>";
    }
}

PDO

// Set database variables
$db_host = "localhost";
$db_user = "root";
$db_pass = "some+password";
$db_name = "sprawdzarka";

// Connect to the database
$pdo = new pdo(
    "mysql:host={$db_host};dbname={$db_name}",
    $db_user,
    $db_pass,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => FALSE
    ]
);

// Set the search parameter to value from $_POST, $_GET, or NULL
$search = ( $_POST["search"] ?? NULL ) ?: ( $_GET["search"]  ?? NULL );

// Check to see if the search URL was submitted
if ( $search ){
    $sql   = "SELECT * FROM domeny WHERE domena = ?";  // Prepare query string
    $query = $pdo->prepare($sql);                      // Prepare query
    $query->execute([$search]);                        // Execute query and bind parameters

    // Check to see if a result was returned
    if( $row = $query->fetchObject() ){
        // Print out the table row
        echo "
            <tr>
                <td>Domain:     {$row->domena}    </td>
                <td>Belongs to: {$row->handlowiec}</td>
            </tr>
        ";
    }
    else{
        // Print out if no results were returned
        echo "This domain doesn't exist: <a href=\"{$search}\">{$search}</a>";
    }
}

solution · Answer 3 · 2020-12-24T23:28:30.213

-1

You can use mysqli_fetch_assoc() (you can also use mysqli_fetch_array() as you did) to get the associative array. then check if it's empty or not then print what you want.See example below.

Also, you are currently exposed to sql injection. Use PDO. Or you can escape harmful characters but best option use the PDO version

$data = mysqli_fetch_assoc($result);      
if( $data ){ // or !empty($data) 
     //1-you can use forloop or while if you don't receive just one results
     //2-if you know you get just one result or you can limit the results you get from 
     //sql statement so you know you have just one.  
     echo "domain exist, print domain name and owner";
     echo"Domain : {$data["domena"]}"
     echo"Owner : {$data["handlowiec"]}"
 }else{ echo"Domain does not exist";}

you can also check using mysql_num_rows($data)!=0

$data = mysqli_fetch_assoc($result);      
if(mysqli_num_rows($data)!=0){
                 
   echo "domain exist, print domain name and owner";
   echo"Domain : {$data["domena"]}"
   echo"Owner : {$data["handlowiec"]}"
}else{ echo"Domain does not exist";}

edited Dec 24 '20 at 23:28

answered Dec 24 '20 at 23:05

solution

24
4

Why `&& !empty($data)`? That seems completely redundant – Dharman Dec 24 '20 at 23:06
`mysql_num_rows` Typo – Dharman Dec 24 '20 at 23:07
you can take it out if you want. But i use it most of the time just to be sure am getting something. – solution Dec 24 '20 at 23:11
1

`PDO` and `escape` characters aren't solutions to prevent injections. Parameterized queries with prepared statements are. `PDO->query` would still be injected – user3783243 Dec 24 '20 at 23:12
I would call you paranoid since that will never do anything useful. `$data` will already be either an empty array, false, or it will actually hold data. This will evaluate to either true or false, so there is no need for any other check – Dharman Dec 24 '20 at 23:13
I mentioned PDO because PDO has the prepared statements. But thanks for the correction – solution Dec 24 '20 at 23:24
Dharman you are right. But i am Paranoid. lol. – solution Dec 24 '20 at 23:25

Query DB if record exist and echo answer if not

3 Answers3

Problems

Solution(s)