How to retrieve a default row when a mysqli_stmt_fetch fails?


Problem :

I've got a simple PHP script that pull a row from a MySQL database based on a URL GET parameter, just an int called, say, ID.

If the GET parameter were to be an invalid ID for the database, such as someone enters an int that's not a row in the DB, or they try and enter a character or something, I'd like to display a default record.

The following code actually works, but I feel that it's bad code. I'm just not sure why, or what I should do. I know I should validate ID is an int before I run the database queries, but that doesn't solve what to do when an integer that is an invalid ID is used.

So to make this a proper question, how should I recover from mysqli_stmt_fetch returning false?

Go easy, I know I'm a nub :-)

$ID = (int)$_GET["id"];

$query = "SELECT `col1`, `col2`, `col3` FROM `table` WHERE `ID` = ?";

if ($stmt = mysqli_prepare($connection, $query))
{
    mysqli_stmt_bind_param($stmt, "i", $ID);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $var1, $var2, $var3);
    if (!mysqli_stmt_fetch($stmt))
    {   
        $ID = 1; //A record I know exists and will always exist
        mysqli_stmt_bind_param($stmt, "i", $ID);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_bind_result($stmt, $var1, $var2, $var3);
        mysqli_stmt_fetch($stmt);
    }
    mysqli_stmt_close($stmt);
}

OK, so with some feedback and experimentation I've cast the ID as an int in PHP and changed the SQL query to this:

SELECT `col1`, `col2`, `col3`
FROM `table`
WHERE `ID` = ?
UNION
SELECT `col1`, `col2`, `col3`
FROM `table`
WHERE `ID` = 1
LIMIT 1

This works for all cases I can think of and seems to be secure. Any problems? Any better than what I had?



Solution :

First check incoming $ID for char and so and set it to value that never be as ID in your table, then use query

SELECT col1, col2, col3 FROM table WHERE ID = :id
union all 
SELECT col1, col2, col3 FROM table WHERE ID = 1 /* may :defaut_id if */
where not exist (select 1 from table WHERE ID = :id)

Mysql Tutorials

Mysql Howto..

How to escape '\n' in Perl for a MySQL query?

How to use UUID in a VARCHAR column with Slick?

How to sort rows of HTML table that are called from MySQL

how to find duplicates and gaps in this scenario in mysql

How to model parent to child pair in MySQL (SQL)

How to reformat dates in m/d/y with in PHP array from jqgrid post data?

How to create an availability script using PHP and MySQL based on schedule entries?

MySQL - How to transfer values from one table to another depending on criteria

How to create and insert a JSON object using MySQL queries?

How do I get unique values from a column and group them with values from another column?

how to calculate a bill from several tables on mysql?

how to fix correlated subquery on mysql

How to copy data from one table to another new table in MySQL?

How to order by most results in MySQL?

MySql how to union 2 queries within if statements

how to build PHP/MySQL query to input data from a series of checkboxes

java-how to get number class files executed by particuler test class from sonar qube data base

How to select a random row in MySQL and PHP? [duplicate]

How to make a multi-join query in MySQL?

How to connect android phone to a web service run on local server?

How to perform several calculations in MySQL?

How to flatten results from table-valued function in MySQL?

How to cope with broken relationships in Grails/GORM?

Java PreparedStatement Delete Query: Show Number of Rows Deleted?

How to extract field name from table having values as 1?

How to sort items in mysql based on data from another table?

how to write a loop to do the calculation in mysql

how to import large datasets to mysql - the right way?

How to create new button/functionality in Gmail via extension?

How to set value of 'a' depending on value of 'b' in mysql