Page 1 of 1

Getting info from DB

Posted: Mon Sep 01, 2014 11:11 pm
by donny
hello,

i want to know if i am doing this correctly. i am pulling info out of my database but i am pretty sure i am using a script thats made for if i was posting results into a table

Code: Select all

<?
$tablesql2 = "SELECT * FROM `orders` WHERE ordernumber ='$ordernumber'";
$tablesqlresult2 = mysql_query($tablesql2) or die(mysql_error());

$num_rows2 = mysql_num_rows($tablesqlresult2);

        if ( 0==$num_rows2 ) {

                echo "  <tr>
    <th class=tg2-ygl1>no email listed</th>
  </tr></table>";

        }

        else {


                while($tablesqlrow2 = mysql_fetch_array($tablesqlresult2)) {

?>
  <tr>
    <td class="tg2-031e"><input type="text" id="shippingemail" name="shipping_email" value="<?php echo $tablesqlrow2['orderemail']; ?>"></td>
  </tr>
  <tr>
    <td class="tg2-3rvq"><input type="submit" value="Update E-Mail Address"></td>
  </tr>
</form>
<?php

                }

        }

?>
is there a better way of doing this.. there will obviously only be 1 email address

for example is there a way i can get this variable $tablesqlrow2['orderemail']; without going through all that trouble. because it will only work in that while().. is there a way i can pull information out of a db at anytime of my page by assigning $tablesqlrow2 to a code so i can pull it out wherever in my code?


thank you

Re: Getting info from DB

Posted: Tue Sep 02, 2014 6:47 am
by Celauran
First, you're using the deprecated mysql_ functions. Best to stop doing that now before it becomes a bad habit. Take a few minutes to become familiar with PDO. Next, all you really want is the orderemail column, so don't SELECT *. In fact, you really shouldn't ever SELECT *. Be explicit about what you want. If the table schema changes, this will prevent your code from pulling in additional columns and will produce an error if you're trying to fetch a column that no longer exists. Better to be aware of that problem and fix it than to allow it to fail silently.

Now for the question at hand, you could wrap this functionality in a function like so

Code: Select all

function getOrderEmail($pdo, $order) {
	$query = "SELECT orderemail FROM `orders` WHERE ordernumber = :order";
	$stmt = $pdo->prepare($query);
	$exec = $stmt->execute([':order' => $order]);

	return $exec ? $stmt->fetchColumn() : '';
}
Pass in your PDO object and the order number you're fetching the email address for, and it will return the email address if it finds it, or an empty string otherwise.

Re: Getting info from DB

Posted: Tue Sep 02, 2014 12:44 pm
by donny
what does the * do in SELECT *
whys it bad to use mysql functions? after i get my site working the right way and every things finished i will go in and change all my mysql functions to PDO. if i do it that way will my querys remain the same? will it be a easy swap?
is that code you made for PDO?

Re: Getting info from DB

Posted: Tue Sep 02, 2014 12:48 pm
by Celauran
SELECT * just means select everything. It's a bad idea for the reasons I listed above.

The mysql_ functions have been deprecated and will be removed from PHP. (Source) See also Choosing an API. The official PHP site has recommended using mysqli or PDO over mysql for longer than I can recall. Years, at any rate. mysql_ doesn't support transactions and doesn't support prepared statements, both of which are important functionality.

Re: Getting info from DB

Posted: Tue Sep 02, 2014 12:49 pm
by Celauran
donny wrote:is that code you made for PDO?
Yes, it's for PDO.

Re: Getting info from DB

Posted: Tue Sep 02, 2014 12:51 pm
by Celauran
I'll be honest, it's not going to be a straight drop in replacement. The queries themselves won't need changing, but all the syntax surrounding them will. The earlier you start, the less code you'll have to change. More importantly, using prepared statements means not having to worry about SQL injection.

Re: Getting info from DB

Posted: Tue Sep 02, 2014 12:59 pm
by donny

Code: Select all


mysql_connect("localhost","user","pass");
mysql_select_db("db");
$ordersql = "UPDATE `orders` SET `orderemail` = '$shippingemail'
WHERE `ordernumber` = '$ordernumber'";
$result1 = mysql_query($ordersql);
if($result1)
{
 echo("
EMAIL UPDATED");
}
else
{
echo("
EMAIL UPDATE FAILED");
}

can you please show me this same code in PDO?

Re: Getting info from DB

Posted: Tue Sep 02, 2014 1:17 pm
by Celauran

Code: Select all

$pdo = new PDO('mysql:hostname=localhost;dbname=db', 'user', 'pass');

$query = "UPDATE `orders` SET `orderemail` = :email WHERE `ordernumber` = :number";
$stmt = $pdo->prepare($query);
$exec = $stmt->execute([':email' => $shippingemail, ':number' => $ordernumber]);
echo $exec ? "Email updated" : "Email update failed";

Re: Getting info from DB

Posted: Tue Sep 02, 2014 1:18 pm
by Celauran

Re: Getting info from DB

Posted: Tue Sep 02, 2014 1:19 pm
by donny
thanks a lot