Can connect to DB but can't extract data

[rtsanderson]

Recently, the code stopped working on one of my sites. I made several attempts to fix it but couldn't find anything wrong. I checked two more sites that used similar code and they had stopped working as well so I assume that something changed in PHP. I've spent several hours trying to find the problem but haven't succeeded. I receive confirmation that I am connecting to my database but the code does not return any records. Any help would be greatly appreciated.

<?php

$servername = "mysql.woodjoint.com";
$username = "MyUsername";
$password = "MyPassword";
$db = "woodjoint";

$conn = mysqli_connect($servername, $username, $password,$db);
if (!$conn) {die("connection failed: " . mysqli_connect_error());
}

echo "<p>connection successful</p>";  /* THIS WORKS */
   
   $sql = 'SELECT * FROM shows';
   mysql_select_db('woodjoint');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Title :{$row['Title']}  <br> ".
         "Venue : {$row['Venue']} <br> ".
         "Address : {$row['Address']} <br> ".
         "--------------------------------<br>";
   }
   
   echo "Fetched data successfully\n";
   
   mysql_close($conn);
?>

[Barand]

Call mysqli_report just before you call mysqli_connect

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

$conn = mysqli_connect($servername, $username, $password,$db);

. . .

Ensure php error_reporting is on and tht error_display is also on.

[cyberRobot]

It looks like you're using MySQLi to connect, but using the old MySQL functions for the rest. Note that the old functions were removed in PHP 7.0. See the warning here:
https://www.php.net/manual/en/function.mysql-query.php

[rtsanderson]

Thanks. My original code was all MySQLi but when that stopped working, I grabbed some code from an online tutorial which was apparently out of date.

I was originally using this method but I know that mysqli_result was deprecated and had added a function to permit the use of it. That worked for years. My suspicion is that the function no longer works.

$i=0;
    while ($i < $num) {
    $Title=mysqli_result($result,$i,"Title,");

$i++; }

This is the function:

      function mysqli_result($res,$row=0,$col=0){
    $numrows = mysqli_num_rows($res);
    if ($numrows && $row <= ($numrows-1) && $row >=0){
        mysqli_data_seek($res,$row);
        $resrow = (is_numeric($col)) ? mysqli_fetch_row($res) : mysqli_fetch_assoc($res);
        if (isset($resrow[$col])){
            return $resrow[$col];
        }
    }
    return false;

[Barand]

What a waste of effort.

Try

$servername = "mysql.woodjoint.com";
$username = "MyUsername";
$password = "MyPassword";
$db = "woodjoint";

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$conn = mysqli_connect($servername, $username, $password, $db);
   
   $sql = 'SELECT title
                , venue
                , address
           FROM shows';
   $result = $conn->query($sql);
   
   foreach ($result as $row) {
       echo "Title :{$row['Title']}  <br> ".
           "Venue : {$row['Venue']} <br> ".
           "Address : {$row['Address']} <br> ".
           "--------------------------------<br>";
   }

Specify the fields you need in the SELECT, not "*".

You specified the default db when you connected so no need to select the db again.

With a mysqli result object (but not with a statement object if you  used a prepared query) you can simply use foreach() to loop through the results.

My advice is to switch to PDO for future projects.

[ginerjm]

Yes!  Definitely learn to use PDO.  So much cleaner and easier to implement.

[rtsanderson]

I managed to get it working with the query

$sql = "SELECT * FROM shows";

But it won't work with this one. The page won't load at all. It used to work. Any ideas?

$sql = "SELECT * FROM shows WHERE DateClose >= now() OR DateOpen >= now() ORDER BY DateOpen";

I'll definitely look into PDO

Edited October 31, 2023 by rtsanderson

[Barand]

What are theactual date values in your database that you expect be found by the WHERE clause?

[ginerjm]

Do you get any error message?  Do you have php error checking enabled?

[rtsanderson]

I found the problem. There was a line of code that checked to see if there were any records. Since no record met the criteria, the code broke.  I removed the line and everything is working now. Thanks everyone for the help.