14 Replies - 853 Views - Last Post: 07 December 2011 - 07:23 PM Rate Topic: -----

#1 Btu  Icon User is offline

  • D.I.C Regular

Reputation: 36
  • View blog
  • Posts: 250
  • Joined: 16-May 11

persistent connection to mysql database

Posted 07 December 2011 - 03:05 PM

Hi there,

I'm trying to learn how to keep a persistent connection open to my database. What I have is a page with buttons and a table that loads a list of courses and their related assignments.

I have a login script:
<?php
ob_start();
$host="mydb.com";
$username="username";
$password="password";
$db_name="mydb";
$tbl_name="admin";

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

$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];

$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql);


$count=mysql_num_rows($result);

if($count==1){
session_register("myusername");
session_register("mypassword");
header("location:main.php");
echo "<h2>Welcome " . $sql['id'] . "</h2>";
}
else {
echo "Wrong Username or Password";
}

ob_end_flush();
?>


My question is, how can I use the same connection to run queries on the DB so I don't have to open/query/close connections every time?

Is This A Good Question/Topic? 0
  • +

Replies To: persistent connection to mysql database

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: persistent connection to mysql database

Posted 07 December 2011 - 03:19 PM

It's not too difficult. First you assign the connection as a resource like so:
$con = mysql_connect("$host", "$username", "$password") or die("cannot connect");


Then any queries you want to run, you run against that resource:
$result=mysql_query($sql, $con);  



That's the basics of it, but I'm sure there are PHP gurus about who will have a better idea / explanation than myself.
Was This Post Helpful? 0
  • +
  • -

#3 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2993
  • View blog
  • Posts: 10,342
  • Joined: 08-August 08

Re: persistent connection to mysql database

Posted 07 December 2011 - 03:21 PM

http://php.net/manua...connections.php
Was This Post Helpful? 0
  • +
  • -

#4 BetaWar  Icon User is offline

  • #include "soul.h"
  • member icon

Reputation: 1148
  • View blog
  • Posts: 7,149
  • Joined: 07-September 06

Re: persistent connection to mysql database

Posted 07 December 2011 - 03:27 PM

The trick is that you don't actually need the $con, but it does offer a way to make things more secure.

If you have an open connection and tell PHP to run a query, or do a mysql call it will use either (A.) the connection you pass into the function, or (B.) the last connection that was open.

That means that this script:
<?php
ob_start();
$host="mydb.com";
$username="username";
$password="password";
$db_name="mydb";
$tbl_name="admin";

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

$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];

$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql);

// NOTICE - Second query here without opening a new connection
$query2 = "SELECT DATABASE()";
$result2 = mysql_query($query2);

$count=mysql_num_rows($result);

if($count==1){
session_register("myusername");
session_register("mypassword");
header("location:main.php");
echo "<h2>Welcome " . $sql['id'] . "</h2>";
}
else {
echo "Wrong Username or Password";
}

ob_end_flush();
?>



Will run the same as this:
<?php
ob_start();
$host="mydb.com";
$username="username";
$password="password";
$db_name="mydb";
$tbl_name="admin";

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

$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];

$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername, $con);
$mypassword = mysql_real_escape_string($mypassword, $con);

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql, $con);

// NOTICE - Second query here without opening a new connection
$query2 = "SELECT DATABASE()";
$result2 = mysql_query($query2, $con);

$count=mysql_num_rows($result, $con);

if($count==1){
session_register("myusername");
session_register("mypassword");
header("location:main.php");
echo "<h2>Welcome " . $sql['id'] . "</h2>";
}
else {
echo "Wrong Username or Password";
}

ob_end_flush();
?>


One other note. Don't forget to call mysql_close(); to close the connection at the end of the page load. Otherwise it will have to timeout, and that's just messy.
Was This Post Helpful? 1
  • +
  • -

#5 Btu  Icon User is offline

  • D.I.C Regular

Reputation: 36
  • View blog
  • Posts: 250
  • Joined: 16-May 11

Re: persistent connection to mysql database

Posted 07 December 2011 - 03:28 PM

BetaWar: That was the answer I was looking for.

Thanks again!

This post has been edited by Btu: 07 December 2011 - 03:30 PM

Was This Post Helpful? 0
  • +
  • -

#6 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2993
  • View blog
  • Posts: 10,342
  • Joined: 08-August 08

Re: persistent connection to mysql database

Posted 07 December 2011 - 03:32 PM

Of course you really should use prepared statements, so here's a link showing PDO's attribute constants, one of which is persistent.

I usually open once and leave out the close connection. If you haven't requested a persistent connection it will (usually) close anyway.
Was This Post Helpful? 2
  • +
  • -

#7 BetaWar  Icon User is offline

  • #include "soul.h"
  • member icon

Reputation: 1148
  • View blog
  • Posts: 7,149
  • Joined: 07-September 06

Re: persistent connection to mysql database

Posted 07 December 2011 - 03:35 PM

Every time a page loads you have to reconnect. This is because the PHP isn't running when it doesn't need to. It runs until a page is loaded (or its timeout has happened) and then stops until it is loaded again.

Basically, all you really want to do is query anything you need from the database for that page load and get out of there. So, say you want to get all the posts by a user as well as that user's user information. You would run a query (or multiple queries) to get all that information and then format it in a pretty way for the browser. Once that is done you would close the connection and be done with it until the next time you had to do it.

Sadly, persistence as in 1 database connection across multiple users viewing multiple pages can't be done easily if at all in PHP. But you can use a persistent connection for each page, if that makes sense; meaning that you only connect 1 time per page load and can do all of your queries with that 1 connection.

<edit>
Gah, @CTphpnwb has an answer that will work out nicely. You can pretty safely disregard this post :)
</edit>
Was This Post Helpful? 0
  • +
  • -

#8 Btu  Icon User is offline

  • D.I.C Regular

Reputation: 36
  • View blog
  • Posts: 250
  • Joined: 16-May 11

Re: persistent connection to mysql database

Posted 07 December 2011 - 03:40 PM

That's some good info, thanks a lot guys.

That being said, on my assignments page, say I have a bunch of work to do... (each requiring a separate query):
Add 2 new assignments (insert)
change teacher name (update)
delete an assignment (delete)

I want to use the same DB connection while I do all this work on the same page right?
Was This Post Helpful? 0
  • +
  • -

#9 BetaWar  Icon User is offline

  • #include "soul.h"
  • member icon

Reputation: 1148
  • View blog
  • Posts: 7,149
  • Joined: 07-September 06

Re: persistent connection to mysql database

Posted 07 December 2011 - 03:42 PM

Yes, unless for some reason you are using a MySQL user that can read (select), but can't write or modify (insert, update). At which point you would have to change users, and that would require a second connection.

Assuming you are using a root MySQL user or granted a new one the all permission, you don't need to change a thing, and a single connection would suffice quite nicely.
Was This Post Helpful? 0
  • +
  • -

#10 Btu  Icon User is offline

  • D.I.C Regular

Reputation: 36
  • View blog
  • Posts: 250
  • Joined: 16-May 11

Re: persistent connection to mysql database

Posted 07 December 2011 - 04:21 PM

Ok, I've made a huge mess here..
I have all my queries in the script, but how do I run one of them on a button click from jquery?
Wrap them in functions?

As you can see my problem here, running a post within a post, over and over... :
$.post("script.php", {
                    a_class: $("#classes").val(),
                    a_name: $("#asmt_name").val(),
                    a_due: $("#asmt_due").val(),
                    a_info: $("#asmt_info").val()
                }, function (output) {

                $.post("script.php", {
                    q: $("#classes").val()
                }, function (output) {
 $("#class_assignments").html("");
					$("#class_assignments").html(output);





The queries are all in the main scope of the PHP script, which I do realize is not correct.

<?

// Connection started
$con = mysql_pconnect('myDB.com', 'btu', 'pass');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("MyDB", $con);
// End connection start



// Get class list
$q=$_POST["q"];
$class="SELECT class_name, class_teacher FROM classes WHERE class_id = '".$q."'";
$sql="SELECT * FROM assignments WHERE assignments.class_id = '".$q."'";

$result = mysql_query($sql);
$class_result = mysql_query($class);

$showclass = mysql_fetch_array($class_result);
echo "<h1>" . $showclass['class_name'] . "</h1>";
echo "<h2 id=\"teacher\">" . $showclass['class_teacher'] . "</h2>";
echo "<table id=\"asmnt_tbl\">";
  echo "<tr>";
  echo "<th>ID</th>";
  echo "<th>Assignment</th>";
  echo "<th>Due</th>";
  echo "<th>Info</th>";
echo "</tr>";


while($row = mysql_fetch_array($result))
  {

  echo "<tr>";
  echo "<td><i>" . $row['assignment_id'] . "</i></td>";
  echo "<td>" . $row['assignment_name'] . "</td>";
  echo "<td>" . $row['assignment_due'] . "</td>";
   echo "<td><textarea cols=\"40\" rows=\"5\">" . $row['assignment_info'] . "</textarea></td>";


  echo "</tr>";
  }
echo "</table>";
// End

// Add assignment to class
$a_name=$_POST["a_name"];
$a_due=$_POST["a_due"];
$a_info=$_POST["a_info"];
$a_class=$_POST["a_class"];
$insert_asmt="INSERT INTO assignments (class_id, assignment_name, assignment_due, assignment_info)
		VALUES ('$_POST[a_class]','$_POST[a_name]','$_POST[a_due]','$_POST[a_info]')" ;


if (!mysql_query($insert_asmt,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";
// End



// Class list
$class_list="SELECT * FROM classes" ;

$result = mysql_query($class_list);
while($row = mysql_fetch_array($result))
  {
echo "$row";
  }
// End

// Update Info
$u_asmt=$_POST["u_asmt"];
$u_info=$_POST["u_info"];
$change_info="UPDATE assignments SET assignment_info = '$u_info'
WHERE assignment_id = '$u_asmt'";


if (!mysql_query($change_info,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "$u_asmt" . " " . "$u_info";
// End

// Change Teacher
$c_teacher=$_POST["c_teacher"];
$c_class=$_POST["c_class"];
$change_teacher="UPDATE classes SET class_teacher = '$c_teacher'
WHERE class_id = '$c_class'";


if (!mysql_query($change_teacher,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "$c_teacher";
// End
// Delete assignment
$class_id=$_POST["id"];
$delete_asmt="DELETE FROM assignments WHERE assignment_id='$class_id'";


if (!mysql_query($delete_asmt,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record deleted";
// End
?>


My page did exactly what I wanted it to do (before i mutilated it) but I just know I'm not doing it properly...
Any suggestions are appreciated (even if it means starting from scratch) ..

This post has been edited by Btu: 07 December 2011 - 04:24 PM

Was This Post Helpful? 0
  • +
  • -

#11 BetaWar  Icon User is offline

  • #include "soul.h"
  • member icon

Reputation: 1148
  • View blog
  • Posts: 7,149
  • Joined: 07-September 06

Re: persistent connection to mysql database

Posted 07 December 2011 - 04:28 PM

Just put all the post variables into 1 Ajax post:
$.post("script.php", {
                    a_class: $("#classes").val(),
                    a_name: $("#asmt_name").val(),
                    a_due: $("#asmt_due").val(),
                    a_info: $("#asmt_info").val(),
                    q: $("#classes").val()
                }, function (output) {
 $("#class_assignments").html("");
					$("#class_assignments").html(output);




That way your other code can still get all of the stuff it needs to run properly, but it doesn't need to be called multiple times.

Also, I would suggest that you minimize the number of repeated variables you have. For instance, right now a_class and q have the same values, so you don't need to send both of them (granted you will have to update the PHP to reflect the change).

Another quick thing - don't forget to sanitize your inputs. While you are getting everything from a post form, there isn't any guarantee that the form came from your site, or that it is still the same form you made originally. There are a lot of ways to spoof or change what the browsers are sending in post data, and any of those could be used to do things like delete all your database information.
Was This Post Helpful? 1
  • +
  • -

#12 Btu  Icon User is offline

  • D.I.C Regular

Reputation: 36
  • View blog
  • Posts: 250
  • Joined: 16-May 11

Re: persistent connection to mysql database

Posted 07 December 2011 - 04:56 PM

Ok, this is starting to make sense now.

But won't it run all the queries in the script?

<?

// Connection started

$con = mysql_pconnect('mydb', 'btu', 'pass');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("mydb", $con);
// End connection start

// Variables

$q=$_POST["q"]; // Class ID
$u_asmt=$_POST["u_asmt"]; //Assignment ID
$u_info=$_POST["u_info"]; // Updated Info
$a_name=$_POST["a_name"]; // Assignment Name
$a_due=$_POST["a_due"]; // Assignment Due
$a_info=$_POST["a_info"]; // Assignment Info
// End


//Queries

$class="SELECT class_name, class_teacher FROM classes WHERE class_id = '".$q."'";
$insert_asmt="INSERT INTO assignments (class_id, assignment_name, assignment_due, assignment_info)
		VALUES ('$_POST[a_class]','$_POST[a_name]','$_POST[a_due]','$_POST[a_info]')" ;
$change_info="UPDATE assignments SET assignment_info = '$u_info' WHERE assignment_id = '$u_asmt'";
$sql="SELECT * FROM assignments WHERE assignments.class_id = '".$q."'";
$class_list="SELECT * FROM classes" ;
$change_teacher="UPDATE classes SET class_teacher = '$c_teacher'
WHERE class_id = '$c_class'";
$delete_asmt="DELETE FROM assignments WHERE assignment_id='$class_id'";
//End

// Get class list

$result = mysql_query($sql);
$class_result = mysql_query($class);

$showclass = mysql_fetch_array($class_result);
echo "<h1>" . $showclass['class_name'] . "</h1>";
echo "<h2 id=\"teacher\">" . $showclass['class_teacher'] . "</h2>";
echo "<table id=\"asmnt_tbl\">";
  echo "<tr>";
  echo "<th>ID</th>";
  echo "<th>Assignment</th>";
  echo "<th>Due</th>";
  echo "<th>Info</th>";
echo "</tr>";
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td><i>" . $row['assignment_id'] . "</i></td>";
  echo "<td>" . $row['assignment_name'] . "</td>";
  echo "<td>" . $row['assignment_due'] . "</td>";
   echo "<td><textarea cols=\"40\" rows=\"5\">" . $row['assignment_info'] . "</textarea></td>";
  echo "</tr>";
  }
echo "</table>";
// End

// Add assignment to class

if (!mysql_query($insert_asmt,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";
// End



// Class list

$result = mysql_query($class_list);
while($row = mysql_fetch_array($result))
  {
echo "$row";
  }
// End

// Update Info

if (!mysql_query($change_info,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "$u_asmt" . " " . "$u_info";
// End

// Change Teacher
$c_teacher=$_POST["c_teacher"];
$c_class=$_POST["c_class"];

if (!mysql_query($change_teacher,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "$c_teacher";
// End

// Delete assignment

if (!mysql_query($delete_asmt,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record deleted";
// End
?>

Was This Post Helpful? 0
  • +
  • -

#13 BetaWar  Icon User is offline

  • #include "soul.h"
  • member icon

Reputation: 1148
  • View blog
  • Posts: 7,149
  • Joined: 07-September 06

Re: persistent connection to mysql database

Posted 07 December 2011 - 05:56 PM

Yes, unless you give it a way to know which queries it is supposed to run. Like a "run this function" variable that you send in.
Was This Post Helpful? 1
  • +
  • -

#14 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2993
  • View blog
  • Posts: 10,342
  • Joined: 08-August 08

Re: persistent connection to mysql database

Posted 07 December 2011 - 07:19 PM

You should NOT be learning/using any mysql_* functions. They're not yet deprecated, but will be because they're insecure and prepared statements are very secure. Use PDO or MySQLi prepared statements.

This post has been edited by CTphpnwb: 07 December 2011 - 07:37 PM
Reason for edit:: spelling :(

Was This Post Helpful? 1
  • +
  • -

#15 Btu  Icon User is offline

  • D.I.C Regular

Reputation: 36
  • View blog
  • Posts: 250
  • Joined: 16-May 11

Re: persistent connection to mysql database

Posted 07 December 2011 - 07:23 PM

a big thanks out to everyone who helped out on this thread
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1