PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sat May 30, 2020 11:30 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 10 posts ] 
Author Message
 Post subject: Getting info from DB
PostPosted: Mon Sep 01, 2014 11:11 pm 
Offline
Forum Contributor

Joined: Mon Aug 11, 2014 11:18 am
Posts: 179
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
Syntax: [ Download ] [ Hide ]
<?
$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


Top
 Profile  
 
 Post subject: Re: Getting info from DB
PostPosted: Tue Sep 02, 2014 6:47 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
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
Syntax: [ Download ] [ Hide ]
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.

_________________


Top
 Profile  
 
 Post subject: Re: Getting info from DB
PostPosted: Tue Sep 02, 2014 12:44 pm 
Offline
Forum Contributor

Joined: Mon Aug 11, 2014 11:18 am
Posts: 179
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?


Top
 Profile  
 
 Post subject: Re: Getting info from DB
PostPosted: Tue Sep 02, 2014 12:48 pm 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
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. () See also . 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.

_________________


Top
 Profile  
 
 Post subject: Re: Getting info from DB
PostPosted: Tue Sep 02, 2014 12:49 pm 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada

_________________


Top
 Profile  
 
 Post subject: Re: Getting info from DB
PostPosted: Tue Sep 02, 2014 12:51 pm 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
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.

_________________


Top
 Profile  
 
 Post subject: Re: Getting info from DB
PostPosted: Tue Sep 02, 2014 12:59 pm 
Offline
Forum Contributor

Joined: Mon Aug 11, 2014 11:18 am
Posts: 179
Syntax: [ Download ] [ Hide ]

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?


Top
 Profile  
 
 Post subject: Re: Getting info from DB
PostPosted: Tue Sep 02, 2014 1:17 pm 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
Syntax: [ Download ] [ Hide ]
$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";

_________________


Top
 Profile  
 
 Post subject: Re: Getting info from DB
PostPosted: Tue Sep 02, 2014 1:18 pm 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada

_________________


Top
 Profile  
 
 Post subject: Re: Getting info from DB
PostPosted: Tue Sep 02, 2014 1:19 pm 
Offline
Forum Contributor

Joined: Mon Aug 11, 2014 11:18 am
Posts: 179
thanks a lot


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: Google [Bot] and 18 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group