Confusion with new API mysqli

bemore

So, I'm new to PHP. I spent a lot of time today teaching myself in the ways of inserting data into SQL tables via html forms with PHP and MySQL, and retrieving said data so it can be displayed on a web page. I felt proud of myself when I had finally coded a working, though very simple system for this. Now I find out that there is newer, more secure API available and that 'mysql' functions will no longer be supported... which my noob code is full of. I've been trying to figure out how to update my code with the new API (MySQLi) for about 3 hours but everything is ending in failure, I'm just not competent enough in PHP or SQL to understand how the changes represent what was already there and working. I did manage to get a connection using the new functions, but I could not display anything like with the old ones.

I will post the code here, and hopefully someone can help me out in figuring this out because I'm just not getting it.

Here is the code that is calling the information from the database, and displaying it in simple rows.

<?php

$host="HOST";
$username="USRNAME";
$password="PASS";
$db_name="USRNAME";
$tbl_name="TABLE";

mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
?>

<table width="800" border="1" cellspacing="0" cellpadding="3" align="center">

<?php
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td width="75%"><? echo $rows['gamename']; ?></td>
<td width="25%"><? echo $rows['region']; ?></td>
<td width="20%"><? echo $rows['mode']; ?></td>
<td width="75%"><? echo $rows['notes']; ?></td>
</tr>

<?php
}
?>

<?php
mysql_close();
?>

Here is the code for the insert action on form submit.

<?php

$host="HOST";
$username="USRNAME";
$password="PASS";
$db_name="USRNAME";
$tbl_name="TABLE";

mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$gamename=$_POST['gamename'];
$region=$_POST['region'];
if(isset($_POST['mode'])) {
$mode = implode(",", $_POST['mode']);   

} else {
$mode = "";
}
$notes=$_POST['notes'];

$sql="INSERT INTO $tbl_name(gamename, region, mode, notes)VALUES('$gamename', '$region', '$mode', '$notes')";
$result=mysql_query($sql);

$gamename = mysql_escape_string($_POST['gamename']);
$region = mysql_escape_string($_POST['region']);
$notes = mysql_escape_string($_POST['notes']);

if($result){
echo "Thank you for your submission.";
echo "<BR>";
echo "<a href='insert.php'>Back to main page</a>";
}

else {
echo "ERROR";
}
?> 

<?php 
// close connection 
mysql_close();
?>

Weedpacket

Well, as a first cut, use the links given in the warnings on the page for each of the mysql_* functions to take you to the corresponding MySQLi function - for the most part it's a matter of replacing "mysql_foo" with "mysqli_foo". The hairiest part of that process is that the order of function arguments is reversed between [man]mysql_query[/man] and [man]mysqli_query[/man].

sneakyimp

The [man]mysqli[/man] documentation has tons of examples. There are two big differences:
1) mysqli has two styles of usage: an object-oriented one and a procedural one. the old mysql functions were only procedural.
2) the procedural mysqli functions you can use to replace your old mysql functions require you to keep track of your database connection object -- you have to supply it whenever you want to query something.

Another thing -- and this may be a bit much to take in -- is that it is often advantageous to isolate your own code from the raw database functions offered by PHP. That is to say it's often helpful to have your code use your own functions for database access and then your functions use the appropriate database access functions. This allows you to more easily switch to a different database entirely or to upgrade to newer functions without changing all of your code. You would only need to change your own database functions.

bradgrafelman

sneakyimp;11022867 wrote:
2) the procedural mysqli functions ... require you to keep track of your database connection object

Careful - that's a connection resource (man: [man]language.types.resource[/man]), not an object (man: [man]language.types.object[/man]).

sneakyimp;11022867 wrote:
it is often advantageous to isolate your own code from the raw database functions offered by PHP

... by creating/using what is normally referred to as a DAL - Database Abstraction Layer. (Just throwing this in the mix in case the OP is curious to learn more and wants a common keyword to search on.)

bemore

Thank you all for the input. I have just finished updating my display codes since I figure this would be more difficult then the insert action code. It is pulling the information from the database table and displaying it inside of the rows properly on the web page. My question now is, just because it is working.. did I do it right:quiet:

And since this code is used to display data, do I need to include any kind of escapes like I do with the insert action code to send data to the database to protect from injection?

<?php
$link = mysqli_connect('host','user','pass','database');
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$tbl_name="table";

// Retrieve data from database 
$sql="SELECT * FROM $tbl_name";
$result=mysqli_query($link,$sql) or die("Error: ".mysqli_error($link));
?>

<table width="800" border="1" cellspacing="0" cellpadding="3" align="center">

<?php
// Start looping rows in mysql database.
while($rows = mysqli_fetch_array($result, MYSQLI_BOTH))
{

?>
<tr>
<td width="30%"><? echo $rows['gamename']; ?></td>
<td width="25%"><? echo $rows['region']; ?></td>
<td width="20%"><? echo $rows['mode']; ?></td>
<td width="90%"><? echo $rows['notes']; ?></td>
</tr>

<?php
}
?>

<?php
// close MySQL connection 
mysqli_close($link);
?>

Weedpacket

bemore wrote:
And since this code is used to display data, do I need to include any kind of escapes like I do with the insert action code to send data to the database to protect from injection?

Well, that's a decision to be made on your part - but since you're displaying it within an HTML page, you have to decide whether (a) it's possible for the data being displayed to contain HTML, and (b) if it does, whether you want it to be treated as HTML.

<td width="30%"><? echo $rows['gamename']; ?></td>
<td width="25%"><? echo $rows['region']; ?></td>
<td width="20%"><? echo $rows['mode']; ?></td>
<td width="90%"><? echo $rows['notes']; ?></td>