Getting info from DB

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
donny
Forum Contributor
Posts: 179
Joined: Mon Aug 11, 2014 11:18 am

Getting info from DB

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting info from DB

Post 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.
donny
Forum Contributor
Posts: 179
Joined: Mon Aug 11, 2014 11:18 am

Re: Getting info from DB

Post 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?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting info from DB

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting info from DB

Post by Celauran »

donny wrote:is that code you made for PDO?
Yes, it's for PDO.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting info from DB

Post 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.
donny
Forum Contributor
Posts: 179
Joined: Mon Aug 11, 2014 11:18 am

Re: Getting info from DB

Post 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?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting info from DB

Post 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";
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting info from DB

Post by Celauran »

donny
Forum Contributor
Posts: 179
Joined: Mon Aug 11, 2014 11:18 am

Re: Getting info from DB

Post by donny »

thanks a lot
Post Reply