Jump to content



Photo

PHP - SQL Efficiency Questions

sql

  • Please log in to reply
18 replies to this topic

#1 xWhiplash

xWhiplash

    Neowinian Senior

  • Joined: 07-March 08

Posted 20 July 2014 - 18:03

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!




#2 +Seahorsepip

Seahorsepip

    http://seapip.com

  • Tech Issues Solved: 27
  • Joined: 23-January 11
  • Location: Netherlands
  • OS: Windows 8.1 Pro
  • Phone: Nexus 5

Posted 20 July 2014 - 19:45

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



#3 +Seahorsepip

Seahorsepip

    http://seapip.com

  • Tech Issues Solved: 27
  • Joined: 23-January 11
  • Location: Netherlands
  • OS: Windows 8.1 Pro
  • Phone: Nexus 5

Posted 20 July 2014 - 20:11

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



#4 OP xWhiplash

xWhiplash

    Neowinian Senior

  • Joined: 07-March 08

Posted 20 July 2014 - 20:36

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.



#5 +Seahorsepip

Seahorsepip

    http://seapip.com

  • Tech Issues Solved: 27
  • Joined: 23-January 11
  • Location: Netherlands
  • OS: Windows 8.1 Pro
  • Phone: Nexus 5

Posted 20 July 2014 - 20:37

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.



#6 OP xWhiplash

xWhiplash

    Neowinian Senior

  • Joined: 07-March 08

Posted 20 July 2014 - 20:45

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?



#7 +Seahorsepip

Seahorsepip

    http://seapip.com

  • Tech Issues Solved: 27
  • Joined: 23-January 11
  • Location: Netherlands
  • OS: Windows 8.1 Pro
  • Phone: Nexus 5

Posted 20 July 2014 - 20:47

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;


#8 +Seahorsepip

Seahorsepip

    http://seapip.com

  • Tech Issues Solved: 27
  • Joined: 23-January 11
  • Location: Netherlands
  • OS: Windows 8.1 Pro
  • Phone: Nexus 5

Posted 20 July 2014 - 20:55

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 $.



#9 ZakO

ZakO

    Neowinian

  • Tech Issues Solved: 2
  • Joined: 21-September 07
  • Location: Finland

Posted 20 July 2014 - 21:08

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/manua...hp#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.



#10 +Seahorsepip

Seahorsepip

    http://seapip.com

  • Tech Issues Solved: 27
  • Joined: 23-January 11
  • Location: Netherlands
  • OS: Windows 8.1 Pro
  • Phone: Nexus 5

Posted 20 July 2014 - 21:24

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/manua...hp#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.



#11 OP xWhiplash

xWhiplash

    Neowinian Senior

  • Joined: 07-March 08

Posted 20 July 2014 - 21:42

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/manua...hp#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);


#12 +Seahorsepip

Seahorsepip

    http://seapip.com

  • Tech Issues Solved: 27
  • Joined: 23-January 11
  • Location: Netherlands
  • OS: Windows 8.1 Pro
  • Phone: Nexus 5

Posted 20 July 2014 - 22:59

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/manua...hp#example-1754

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

#13 ZakO

ZakO

    Neowinian

  • Tech Issues Solved: 2
  • Joined: 21-September 07
  • Location: Finland

Posted 20 July 2014 - 23:02

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/manua....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

#14 +Seahorsepip

Seahorsepip

    http://seapip.com

  • Tech Issues Solved: 27
  • Joined: 23-January 11
  • Location: Netherlands
  • OS: Windows 8.1 Pro
  • Phone: Nexus 5

Posted 20 July 2014 - 23:16

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

#15 OP xWhiplash

xWhiplash

    Neowinian Senior

  • Joined: 07-March 08

Posted 20 July 2014 - 23:51

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?