Obtaining details from MySQL database (VARCHAR query string issue?)

[Toneboy]

I seem to be confusing myself, so I’ll detail a bit of the background and what I’m trying to do.

I have an archive of what are referred to on the site as “thoughts” in a MySQL database. There are multiple fields here detailing all kinds of things, e.g. author, content, go live time, etc. I previously called these through obtaining the day, month and year of the thought through the query string, e.g. ?d=31&&m12&&y=2007 (to give one example). To help identify each thought in the database I’ve set the id field in a similar format, e.g. “31-12-2007”. Due to this, the id field is set as VARCHAR.

I’ve just moved hosts, partially because I’ve felt the need to move from a host running PHP v5.* to v 7.*. And part of this is to force myself to force myself to get away from old code and call things from the MySQL database the right way.

However, I seem to be confusing myself when trying to call this. I’ve ensured that the query string is being captured and is in the expected format. I’ve gone back and built the page incrementally, and it seems to fall over when I’m getting to the query. Little bit odd as well when I’ve run the query in PHPMyAdmin and it works successfully (admittedly here I pass on what would be the query string as a value such as “31-12-2007” instead of $shortthoughtid).

Queries I’ve attempted are as follows:

$sql = "SELECT * FROM thoughts WHERE id=?"; // SQL with parameters
$stmt = $conn->prepare($sql); 
$stmt->bind_param("s", $shortthoughtid);
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$fullcontent = $result->fetch_assoc(); // fetch data
// Test line to see if query is successful
echo $fullcontent;

$selectThought = $mysqli->prepare("SELECT * FROM thoughts WHERE id = '$shortthoughtid' LIMIT 1");
$selectThought->execute();
$selectThoughtResult = $selectThought->get_result();

    /* now you can fetch the results into an array - NICE */
    while ($myrow = $selectThoughtResult->fetch_assoc()) {

        // use your $myrow array as you would with any other fetch
        printf("This is the title - $myrow['title']);

    }

Also, I could potentially call an article using the go live field, which is an integer, if that would be easier.

Overall, it is a bit baffling when I’ve got other queries that I have been able to translate over and get working. Struggling to get my head around this all and understand it if I’m being honest. Any help appreciated, thanks.

[Thallius]

Maybe not what you are looking for, but just a hint:

If you are already refactoring your code, don’t go from very old code to old code. Use PDO instead of mysqli. In fact this would make your life much easier.

[mabismad]

@Toneboy

I’ve got other queries that I have been able to translate over and get working

Please post an example of a query and its php code that does work.

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 will help you by reporting and displaying all the php errors it detects?

Do you have error handling for all the database statements that can fail - connection, query, prepare, and execute, such as using exceptions for errors and letting php catch them so that php will display or log database statement errors the same as php errors? To enable exceptions for errors for the mysqli extension, add the following line of code before the point where you make the database connection -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Dates should be stored in a database using a DATE datatype. Also, calling them id’s implies that they are unique, with only one row per value. You can convert the existing values to a DATE datatype, which has a YYYY-MM-DD format, by adding a new column of the correct type, and executing one UPDATE query to populate the new column with reformatted values (see the MySql STR_TO_DATE() function.) After you change and test all your code to use the new date column, you can remove the old column. As always, make fundamental changes like this on a development system, operating on a copy of your actual data.

If you are just now updating your code, you should skip trying to use the mysqli extension. The PDO extension is much simpler and better designed.