Ok, so I'm finally make the switch from using mysql functions to mysqli. I've read a bit and have hacked around some old code to experiment with. Before I go much further I'd like to make sure that I'm on the right track with how I'm doing things. I've got lots of scripts to update to use MySQLi and my goal is to change just the data access code and leave all of the rest untouched.

I've taken an old page for a website designer's website that shows the designer's portfolio. The portfolio details are stored in a MySQL database, just a single table that looks like this:

I haven't shown the connection code, but assume that it sets a variable called $db that contains the connection link. I've elected to go with a prepared statement to handle the passed in ID.

The reason I'm using an array called $portfolioRow to hold the result is because the rest of the code that displays the result was already set up to use it and so I thought it made sense to keep it that way. Also, I think it's tidier to keep it all in an array rather than set up lots of individual variables. Also I prefer procedural style over object.

I know this code works ok, but does it follow best practice rules? Should I be doing it any other way or doing more or different error checking?

After showing the details of the passed in ID the script then goes on to list all of the rows in the table, split up into Category sections (i.e. all the rows with Category = "Commercial", then with Category = "Non-Profit", etc) with links that call the page again with a new ID. I do this by putting the main logic in a function and then calling the function once for each category:

Now that you're using mysqli you could look at replacing the query call with separate prepare and bind calls instead so that the data is kept completely separate from the SQL.

aweb4u
—
2013-03-04T22:12:59Z —
#5

felgall said:

Now that you're using mysqli you could look at replacing the query call with separate prepare and bind calls instead so that the data is kept completely separate from the SQL.

You mean in my DisplayCategory() function (because I did use prepare and bind in my first DB access)? So you'd recommend always using mysqli_prepare() and mysqli_stmt_bind_param() over mysqli_query(), even when the query input is coming from a trusted source?

felgall
—
2013-03-05T01:09:53Z —
#6

aweb4u said:

even when the query input is coming from a trusted source?

It makes the difference between injection being unlikely and being impossible. Also there's nothing to prevent the DisplayCategory() function being reused where the data is not from a trusted source.

aweb4u
—
2013-03-05T01:19:50Z —
#7

felgall said:

It makes the difference between injection being unlikely and being impossible. Also there's nothing to prevent the DisplayCategory() function being reused where the data is not from a trusted source.