Issue getting result after prepared statement

Beginners - Learning PHP
#1

I have a database of musicians which is displayed in one page. I managed to get the urls prepared based on the musicians’ IDs for their individual pages to be dynamically created. So, in the individual musician page template, I get the musician’s id from the url parameters using the $_GET function in order query the database again and show their info.
Initially, I had the following SQL query which is prone to SQL injection attacks:

$sql = “SELECT concat(first_names , ’ ’ , last_name) as musician_name, musician_id
FROM musicians
WHERE musician_id=$musid”;

Now, I’m trying to use a placeholder in place of $musid variable and prepare the statement with the following code, but no info is shown (database connection is double checked to be working). I appreciate it if you could point out what I’m doing wrong in this code, in order to get the musicians name displayed as the heading.

/***** Getting the musician ID from the URL *****/
            
            if(isset($_GET['ID'])) 
                {
                     $musid = $_GET['ID'];
                }

/*********** Getting 'musician' info and displaying *********/
            
            $sql = "SELECT concat(first_names , ' ' , last_name) as musician_name, musician_id 
                    FROM musicians 
                    WHERE musician_id=?";
                    
            $stmt = mysqli_stmt_init($conn);
            
            if(!mysqli_stmt_prepare($stmt, $sql))
            {
                echo "query error";
            } else
                {
                mysqli_stmt_bind_param($stmt, "i", $musid);
                mysqli_stmt_execute($stmt);
                $result = mysqli_stmt_get_result($stmt);
                
                while ($row = mysqli_fetch_assoc($result))
                    {
                        echo '<h1>'.$row['musician_name'] .'</h1>';
                    }
                }
#2

The the mysqli stmt get_result() method is only available if php is built and is using the mysqlnd driver. Do you have php’s error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, so that php would help you by reporting and displaying all the errors it detects?

Next, since the $_GET[‘id’] input is ‘required’ for this code to work, all the database specific code needs to be inside that if(){…} statement. It currently isn’t.

The PDO database extension is much simpler and easier to use. It also doesn’t have any gotyas like the one with get_result(). The equivalent PDO code would simply be -

// at the point of getting the data
if(isset($_GET['ID']))
{
	$sql = "SELECT concat(first_names , ' ' , last_name) as musician_name, musician_id
		FROM musicians
		WHERE musician_id=?";
	$stmt = $pdo->prepare($sql);
	$stmt->execute([ $_GET['ID'] ]);
	$musician_data = $stmt->fetchAll();
}

// at the point of producing the output
if(empty($musician_data))
{
	echo 'There is no data to display.';
}
else
{
	foreach($musician_data as $row)
	{
		echo "<h1>{$row['musician_name']}</h1>";
	}
}

Lastly, use exceptions for database statement errors and in most cases simply let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) You can then remove any existing error handling logic you have now (and for the execute() call that doesn’t have any now, you don’t need to add any), since it will no longer get executed upon an error. The only case where you need database error handling in your code is when inserting/updating duplicate or out of range user submitted data, where the user can potentially correct the problem by submitting a different value.

#3

An additional point - if this query will match at most one row of data, don’t use a loop to fetch the data, just directly fetch the single row of data and test if a row was fetched.

#4

Thank you again for the suggestions.

Unfortunately, the code did not work. I suspected whether PDO is enabled in my hosting server, and it has many pdo extensions out of which only “pdo”, “pdo_mysql” and “pdo_sqlite” are enabled (the PHP version is 7.4).
So far, I have not relied on a method to read errors (only the output displayed on the webpage). I will try to set this up so I can see what kind of error messages I get.

Upon seeing your latest message, I removed the for loop and just tried to echo
"<h1>{$row['composer_name']}</h1>" within the else statement.

Basically, it’s supposed to match the ID it fetches from the redirecting url with the ‘musician ID’ column in the database table, find the relevant musician and display only that musicians data in this page (so far just the musician’s name as the heading).

I have everything working without using prepared statements–if you’re interested in seeing that code.

Appreciate any further feedback.

#5

The query isn’t SELECTing anything called composer_name, so, this should be producing a php error. So, back to the first paragraph I wrote, make sure that the php error_reporting and display_errors settings are set as suggested so that php will help you.

#6

You should not be trying to learn, develop, and debug code/query(ies) on a live/public server. This may also be why you are not seeing any errors and may not be able to set php’s error settings. A lot of cheap hosting prevents you from setting these as an attempt to make their hosting more ‘secure’.

#7

My bad. I just corrected the error which came from the old version of this page. Still, it doesn’t show anything though.

By the way, I also have an html section after this, that echos the ‘musician_name’ again (for testing purposes). I must mention that I’m working within a Wordpress theme and I noticed that my footer is gone, which is caused by data not getting into this html section which is the body.

#8

I’ll try setting this up in a local server in my computer.

#9

If you could kindly answer, regarding your initial code, I wonder whether the

$stmt->bind_param(); line not necessary in PDO?

#10

The PDO based code I posted doesn’t have any explicit input binding statement, because you can simply supply an array of the input values to the ->execute([…]) call. Also, bind_param() is specific to the mysqli extension, not the PDO extension. The PDO extension does have bindparam() and bindvalue() statements, but these are NOT necessary in most cases.

#11

Thank you for your help. I managed to get all to work using your initially suggested code! It turns out that I have not properly initiated the database connection according to PDO requirements.

Sponsor our Newsletter | Privacy Policy | Terms of Service