• 0

PHP - SQL Efficiency Questions


Question

Hello,

 

After many years, I have finally decided to build a database for my website.  

 

I have a Video table that has ID, Name, Description, Series_ID, Size, and more.

I have a Resource table that contains the name and location (for links for resources).

I have a Video_Resource table that has the Video ID and the Resource ID.

 

I am new to PHP and MySQL.  I have used SQL Server and ASP.NET before, so I will use that for my examples.  

 

What I need to do is have a page listing all the videos by a specific series ID, which is a simple query, and resources for each video.  Now with ASP.NET I would do something like this:

using (SqlConnection connection = new SqlConnection(connectionString))
{
     connection.Open();
     using (SqlCommand commandA = new SqlCommand("SELECT * FROM Video WHERE Series_ID = @SeriesID", connection))
     {
          commandA.Parameters.Add(new SqlParameter("@SeriesID", SqlDbType.Int)).Value = seriesID;  //from query string for example.

          using(SqlDataReader readerA = commandA.ExecuteReader())
          {
               while(readerA.Read())
               {
                    int videoID = Convert.ToInt32(readerA["ID"]);
                    string name = readerA["Name"].ToString();
                    //more variables
                    using (SqlCommand commandB = new SqlCommand("SELECT * FROM Resource WHERE Video_ID = @VideoID", connection))
                    {
                         commandB.Parameters.Add(new SqlParameter("@VideoID", SqlDbType.Int)).Value = videoID;
                         using (SqlDataReader readerB = commandB.ExecuteReader())
                         {
                              while(readerB.Read())
                              {
                                   string resourceName = readerB["Name"].ToString();
                                   //more variables
                              }
                         }
                    }
               }
          }
     }
}

I know I could combine those into one query:

SELECT v.*, r.* FROM Video AS v JOIN Video_Resource AS vr ON vr.Video_ID = v.ID JOIN Resource AS r ON r.ID = vr.Recource_ID WHERE v.ID = @VideoID

But what I do NOT want to have happen is multiple records since some videos might have 5 or more resources.  I do not want video ID 3 to be displayed 5 times since the query outputted it 5 times.

 

Basically this is what I want my output to be:

 

 

Video Name

-------------------Video Description

-------------------Resource Link

-------------------Resource Link

 

 

Video Name

-------------------Video Description


 

 

Video Name

-------------------Video Description

-------------------Resource Link

-------------------Resource Link

-------------------Resource Link

-------------------Resource Link

-------------------Resource Link

-------------------Resource Link

 

Now my questions:

 

  1. Is there a more efficient way to do this?
  2. I read up a little bit on PHP PDO and MySQLi, can I specify the SQL data type like I can in ASP.NET?

Thanks!

Link to comment
Share on other sites

18 answers to this question

Recommended Posts

  • 0

Use arrays for your videos which you can loop trough, lemme write a example.

Link to comment
Share on other sites

  • 0

Here we go:

$con=mysqli_connect("example.com","user","password","mysql_db");

if (mysqli_connect_errno()) {
	echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT v.*, r.* FROM Video AS v JOIN Video_Resource AS vr ON vr.Video_ID = v.ID JOIN Resource AS r ON r.ID = vr.Recource_ID WHERE v.ID = @VideoID");

$videos= array();

while($row = mysqli_fetch_array($result)) {
	$v = $row[v.*]; //Set current v.* as variable v
	$r = $row[r.*]; //Set current r.* as variable r
	$videos[$v][] = $r; //Add r to array of v
}

To show you that my code works great with these kind of things(multiple times $v variable with different $r variable):

http://ideone.com/1z2rm7

 

To use output above array in the link example to text you can use the following code example:

foreach($videos as $id=>$video) {
	echo "id is ".$id;
	foreach($video as $description){
		echo "description: ".$description;
	}
}

Any other questions or things you want to know?

 

And yes I'm addicted to arrays in php, I use them for everything xD

Link to comment
Share on other sites

  • 0

Thanks!  How do I do parameterized queries in PHP?  Can I specify that the VideoID variable is an Integer like I can in ASP.NET like this:

commandB.Parameters.Add(new SqlParameter("@VideoID", SqlDbType.Int)).Value = videoID;

Here, I specified that the VideoID parameter should be an integer.

Link to comment
Share on other sites

  • 0

I read up a little bit on PHP PDO and MySQLi, can I specify the SQL data type like I can in ASP.NET?

Yes it's possible in PDO and MySQLi don't have much experience with advanced sql queries and datatypes, I always try to keep my sql code to the absolute minimum :p

 

You can convert php code that uses mysql to mysqli in most cases easily by changing "mysql" to "mysqli" in the php code* but it does not always work, some mysql functions will not work as example the following sql query works fine with mysql in php but does not work with mysqli:

SELECT * FROM table WHERE temp="cold" AND time="early"

Using "AND" in the WHERE clause gives problems with mysqli, you need to write "prepared statements" in mysql instead to make it work.

 

*changing mysql query to mysqli query:

//mysqli, uses $con for connection details which also includes the database
$con = mysqli_connect("localhost", "user",  "password", "database");
mysqli_query($con,"SELECT * FROM table");

//mysql, uses $db for connection details and has a database select function to choose the database
$db = mysql_connect("localhost", "user",  "password");
mysql_select_db("database");
mysql_query("SELECT * FROM table");

I never really used PDO much, it's really handy when working with multiple database types since it support multiple database types at the same time with the same code and syntax.

Link to comment
Share on other sites

  • 0

In your previous example, you had the following:

$result = mysqli_query($con,"SELECT v.*, r.* FROM Video AS v JOIN Video_Resource AS vr ON vr.Video_ID = v.ID JOIN Resource AS r ON r.ID = vr.Recource_ID WHERE v.ID = @VideoID");

So if I wanted to get something similar, but for all videos of a series, I would do this:

$result = mysqli_query($con,"SELECT v.*, r.* FROM Video AS v JOIN Video_Resource AS vr ON vr.Video_ID = v.ID JOIN Resource AS r ON r.ID = vr.Recource_ID WHERE v.Series_ID = @SeriesID");

Where would I fill in the @SeriesID parameter?  Lets say I have a page like Video.php?seriesID=3, so I would get the seriesID from the query string of the page.  How do I fill that in the SQL query as a parameter?

Link to comment
Share on other sites

  • 0

Thanks!  How do I do parameterized queries in PHP?  Can I specify that the VideoID variable is an Integer like I can in ASP.NET like this:

commandB.Parameters.Add(new SqlParameter("@VideoID", SqlDbType.Int)).Value = videoID;

Here, I specified that the VideoID parameter should be an integer.

In php you can query a database and get a value from it, depending on the column type the php variable will be set to that variable type.

 

As example if you got a table with a integer column and fetch a value(as example 6) and set it to variable $value you will get the following php value:

$value = 6;

But if you got a table with a string column and fetch a value(as example 6) and set it to variable $value you will get the following php value:

$value = "6";

The " indicates that it's a String instead of a Integer, Double, Float etc.(Double doesn't exist actually in php, it's the same as float)

 

You can convert integers or floats easily to strings with php:

$int = 123
$string = (string) $int; or $string = "$int";

And also you can easily convert a string to a integer:

$string = "456";
$int = intval($string); or $int = (int) $string;

Or convert a string to float:

$string = "789";
$float = floatval($string); or $float = (float) $string;
Link to comment
Share on other sites

  • 0

In your previous example, you had the following:

$result = mysqli_query($con,"SELECT v.*, r.* FROM Video AS v JOIN Video_Resource AS vr ON vr.Video_ID = v.ID JOIN Resource AS r ON r.ID = vr.Recource_ID WHERE v.ID = @VideoID");

So if I wanted to get something similar, but for all videos of a series, I would do this:

$result = mysqli_query($con,"SELECT v.*, r.* FROM Video AS v JOIN Video_Resource AS vr ON vr.Video_ID = v.ID JOIN Resource AS r ON r.ID = vr.Recource_ID WHERE v.Series_ID = @SeriesID");

Where would I fill in the @SeriesID parameter?  Lets say I have a page like Video.php?seriesID=3, so I would get the seriesID from the query string of the page.  How do I fill that in the SQL query as a parameter?

Here's a example showing how to use url parameters:

if (isset($_GET['seriesID'])) {
	$seriesid = $_GET['seriesID'];
}

$result = mysqli_query($con,"SELECT v.*, r.* FROM Video AS v JOIN Video_Resource AS vr ON vr.Video_ID = v.ID JOIN Resource AS r ON r.ID = vr.Recource_ID WHERE v.Series_ID = $seriesid");

As you see it's just a matter of putting your php variable into the mysqli query string, there's no need to work with quotes or anything like that since php automatically sees a $ sign as a php variable that needs to be added into the string.

 

Keep in mind if you actually want to put the word "$seriesid" in the string instead of the value of the php variable you need to escape the php variable by putting \ in front of $.

Link to comment
Share on other sites

  • 0

In your previous example, you had the following:

$result = mysqli_query($con,"SELECT v.*, r.* FROM Video AS v JOIN Video_Resource AS vr ON vr.Video_ID = v.ID JOIN Resource AS r ON r.ID = vr.Recource_ID WHERE v.ID = @VideoID");

So if I wanted to get something similar, but for all videos of a series, I would do this:

$result = mysqli_query($con,"SELECT v.*, r.* FROM Video AS v JOIN Video_Resource AS vr ON vr.Video_ID = v.ID JOIN Resource AS r ON r.ID = vr.Recource_ID WHERE v.Series_ID = @SeriesID");

Where would I fill in the @SeriesID parameter?  Lets say I have a page like Video.php?seriesID=3, so I would get the seriesID from the query string of the page.  How do I fill that in the SQL query as a parameter?

 

The MySQLi extension has support for prepared statements (although not named parameters) using the mysqli_prepare and mysqli_stmt_bind_param functions, there's a basic example of it in the PHP manual: http://php.net/manual/en/mysqli.prepare.php#example-1754. I would definitely take the route of using parametrised queries rather than simply interpolating variables into the query, parametrised queries will ensure all the queries are appropriately protected against SQL injection.

Link to comment
Share on other sites

  • 0

The MySQLi extension has support for prepared statements (although not named parameters) using the mysqli_prepare and mysqli_stmt_bind_param functions, there's a basic example of it in the PHP manual: http://php.net/manual/en/mysqli.prepare.php#example-1754. I would definitely take the route of using parametrised queries rather than simply interpolating variables into the query, parametrised queries will ensure all the queries are appropriately protected against SQL injection.

Yeah you got a point there, in most cases I just do a mysql escape, strip tags and some other things with my php variable before I use it in my mysqli query depending on the mysqli query instead of using prepared statements. 

 

In his case using prepared statements might be a bit too much considering he will only be using numbers, converting the variable from $_get to a integer and checking that the number between two numbers(min and max) will already suffice in security since a sql injection in a variable is rendered useless the moment you convert that value to a integer.

 

The necessity of prepared statements depends on the variable you're using and the query. When the php code is big and has a big chance of errors I agree that using prepared statements is a must if you value the security of your website.

Link to comment
Share on other sites

  • 0

The MySQLi extension has support for prepared statements (although not named parameters) using the mysqli_prepare and mysqli_stmt_bind_param functions, there's a basic example of it in the PHP manual: http://php.net/manual/en/mysqli.prepare.php#example-1754. I would definitely take the route of using parametrised queries rather than simply interpolating variables into the query, parametrised queries will ensure all the queries are appropriately protected against SQL injection.

 

That page has:

if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {

How do I have multiple parameters?

 

Also, what does the s mean here?

/* bind parameters for markers */
    $stmt->bind_param("s", $city);
Link to comment
Share on other sites

  • 0

That page has:

if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {
How do I have multiple parameters?

Also, what does the s mean here?

/* bind parameters for markers */
    $stmt->bind_param("s", $city);
Type specification chars:

i corresponding variable has type integer

d corresponding variable has type double

s corresponding variable has type string

b corresponding variable is a blob and will be sent in packets

The s means that the value requested is a string.

To use multiple items as example a string and a integer:

$stmt->bind_param("si", $name, $age);

This will result in a string with a name and a integer with a age.

http://php.net/manual/en/mysqli.prepare.php#example-1754

See first comment over there on how to work with multiple rows.

Link to comment
Share on other sites

  • 0

That page has:

if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {
How do I have multiple parameters?

 

Also, what does the s mean here?

/* bind parameters for markers */
    $stmt->bind_param("s", $city);
 

You can have multiple parameters simply by having multiple ?'s in the query, then the values are used in the order they're passed to bind_param. The parameters for bind_param function are in the PHP manual http://php.net/manual/en/mysqli-stmt.bind-param.php

 

As a basic example, if you had two strings and one integer you wanted to build a query out of you can use:

if ($stmt = $mysqli->prepare("SELECT nickname FROM Users WHERE firstName=? AND lastName=? AND age=?")) {
    $stmt->bind_param("ssi", $firstName, $lastName, $age); 
    ...
}
edit: my hands, they're too slow! :p
Link to comment
Share on other sites

  • 0

edit: my hands, they're too slow! :p

I win YAY, I'm even on my phone haha :P

We were both thinking of using name and age as example xD

Link to comment
Share on other sites

  • 0

Oh okay I get it now.  I am used to ASP.NET where I would need to make new parameters on every line.

 

So if I was using a Series ID, I would use i instead of an s?  What would happen if I use an i but I pass in a string?

 

Also, Seahorsepip, you had the following code:

while($row = mysqli_fetch_array($result)) {
	$v = $row[v.*]; //Set current v.* as variable v
	$r = $row[r.*]; //Set current r.* as variable r
	$videos[$v][] = $r; //Add r to array of v
}

More specifically:

$videos[$v][] = $r; //Add r to array of v

If a video has multiple resources, does that append to the array, or overwrite the first several resources with that is in the current iteration?

Link to comment
Share on other sites

  • 0

Oh okay I get it now. I am used to ASP.NET where I would need to make new parameters on every line.

So if I was using a Series ID, I would use i instead of an s? What would happen if I use an i but I pass in a string?

Also, Seahorsepip, you had the following code:

while($row = mysqli_fetch_array($result)) {
	$v = $row[v.*]; //Set current v.* as variable v
	$r = $row[r.*]; //Set current r.* as variable r
	$videos[$v][] = $r; //Add r to array of v
}
More specifically:
$videos[$v][] = $r; //Add r to array of v
If a video has multiple resources, does that append to the array, or overwrite the first several resources with that is in the current iteration?
That code appends a resource to the end of the array, it does not overwrite resources. Using [] without a value means that you want to add a value to the end of the array inside videos[$v].

You can also make it even more advanced. Currently the array exists of a list of video ids with a array inside them consisting of sources.

You can also make a array of video ids and inside that a array of video details, so one name, one link, an array with descriptions.

So the array would look like this:

$videos = array();

//Begin while loop
$id = 123;
$name = "video name";
$link = "video link";
$description = array("description one","description two","description three");
$video = array(name=>$name,link=>$link,description=>$description);
$videos[$id] = $video;
//End while loop

print_r($videos);//show array that has been created for testing, if the text looks like a single line add <pre> html tags around this php block
Link to comment
Share on other sites

  • 0

And if you use a i and pass in a string you should get a error, that's the whole point of prepared mysqli queries, you can try yourself and see what happens ^^

Link to comment
Share on other sites

  • 0

Hi,

When dealing with stock PHP / MySQL you will be dealing with unoptimized configurations, in regards to memory management and IO.

You will have a performance difference between PDO and MySQLi where in most instances MySQLi will out perform PDO, however, you will lose a lot of features. Two that might specifically be of interest.

1. Prepared statements (can be done in MySQLi but is native for PDO), which adds a lot of benefits.

2. PDO is not tied to a specific "vendor", which I use the term loosely.

Link to comment
Share on other sites

This topic is now closed to further replies.