Getting info from the database...

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

User avatar
No0b
Forum Commoner
Posts: 37
Joined: Tue Feb 07, 2006 6:17 pm

Getting info from the database...

Post by No0b »

Ok so what I want to know is how I can get the information from the database.
I have used the SELECT clause in this book I'm reading (PHP 5 fast&easy web development) but just don't understand the proccess. First they tell me to make a var call $sql and give it the value of "SELECT * FROM $table_name"

Code: Select all

$sql = "SELECT * FROM $table_name";
$table_name is a var that has the value of my table name. Then they tell me to make a var with the value of mysql_query($sql,$connection);

Code: Select all

$result = mysql_query($sql, $connection);
So what this means is I'm not quite sure. I know this seems like really simple stuff, and I'm a noob... :( But what I think this means is that your selecting all the tables from the $table_name. But what's the mysql_query() function? Why is it called mysql_query()? What's a query in mysql? What does this code even do? Does it return the value of the selected field?
I don't really care if I'm called a noob or anything so don't think it'll bother me. :D

These are only some of the things I'm trying to get at and have awnsered but the rest will be later once these are awnsered and explained and understood.

Thanks for reading and all help is appreciated. :wink:
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Hey no0b (not calling you a noob that's your name!)


A query is some code in a language called simple query language, or SQL for short. A query is anything in SQL to interact with your database server, anything with the word SELECT is asking it to give you data. The 4 most popular are select, update, delete and insert.

In that case you are asking mysql to give you all the data on the table, try something like this

Code: Select all

<?php
mysql_connect('localhost','username','password') or die(mysql_error());
$sql  = "SELECT * FROM `myTable`";
$result = mysql_query($sql) or die(mysql_error());
?>
<table border="3">
<?php
while($row=mysql_fetch_row($result) {
?><tr><?php foreach($row as $data) { echo '<td>'.htmlentities($data).'</td>';  } ?></tr><?
}
?></table><?php
mysql_free_reuslt($result);
?>
The SQL in there is SELECT * FROM myTable, you have a table which can be visualized as an excel spreadsheet, you have columns and rows. You asked it to give you all columns (that's the *) and by default it will return every row. The while loop loops over the rows one at a time and ouputs them, the foreach() that is nested within there loops over each field. The mysql_query() actually sends your SQL code to mysql to be run, since you used select it is returning data. $result is a resource containing the data mysql is returning to you.
User avatar
No0b
Forum Commoner
Posts: 37
Joined: Tue Feb 07, 2006 6:17 pm

Post by No0b »

Ok so I think I get it. Thanks for the reply. So what would the value of $result??? in your code and mine???
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

$result would be the mysqlresult of your query, in this case the data you asked it to select
User avatar
No0b
Forum Commoner
Posts: 37
Joined: Tue Feb 07, 2006 6:17 pm

Post by No0b »

Ok so the $result = to the fields you selected... Makes sence, but how do I only select one field in the database and only the on that has the value in the field username of $_SESSION[username]??? For example:

____________________________________
|username | picture | password | bla bla bla | (fields)
|No0B_____|pic_____|bla bla___|ijsljdfas____|
//More field following
____________________________________

How could i get only the one picture "pic" where the no0b username is??? Would it be:

Code: Select all

$sql = "SELECT picture FROM $table_name WHERE username = '$_SESSION[username]' ";

$result = mysql_query($sql) or die (mysql_error());

echo "$result";
??????????????????????????????????????????????????????????????????????????????????????
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

That would work however it is not a good idea the way you did it:

Code: Select all

$result = mysql_query(
    sprintf(
         "
         SELECT `picture` FROM `table` WHERE `username` = '%s' LIMIT 1
         ",
         mysql_real_escape_string($_SESSION['username'])
    )
);
$picture=mysql_result($result,0,0);
mysql_free_result($result);
the mysql_real_escape_string() function must be used on anything you are putting into the query. The LIMIT 1 tells mysql I only want a maximum of one row back
User avatar
No0b
Forum Commoner
Posts: 37
Joined: Tue Feb 07, 2006 6:17 pm

Post by No0b »

Why is it not a good idea? What's the "%s" mean? I don't really understand it your way. Can you explain everything you did? :? What's the sprintf() function??? 8O
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

It's not a good idea because you can get hacked if you don't use mysql_real_escape_string() sprintf() just inserts the value into the string where %s is, I did that to make the code easier to read, it could be rewritten as

Code: Select all

mysql_query("SELECT `picture` from `table` where `username` = '".mysql_real_escape_string($_SESSION['username'])."' limit 1");
User avatar
No0b
Forum Commoner
Posts: 37
Joined: Tue Feb 07, 2006 6:17 pm

Post by No0b »

Why must the mysql_real_escape_string() function be used when send iformation from the query?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

No0b wrote:Why must the mysql_real_escape_string() function be used when send iformation from the query?
read mysql_real_escape_string() .. key words "SQL injection"
Last edited by John Cartwright on Thu Feb 09, 2006 3:54 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

  1. For security, there's a potential for someone to inject SQL commands into your code, which may do a lot of damage.
  2. Because there are some characters which must be escaped for MySQL to be able to parse the query.
User avatar
nickman013
Forum Regular
Posts: 764
Joined: Sun Aug 14, 2005 12:02 am
Location: Long Island, New York

Post by nickman013 »

I strongly advise you to check out this page, it shows you alot of the MySQL Commands and what they do.

:arrow: http://www.pantz.org/database/mysql/mysqlcommands.shtml
User avatar
No0b
Forum Commoner
Posts: 37
Joined: Tue Feb 07, 2006 6:17 pm

Post by No0b »

So are you guys saying that someone will try to sign up with a username as a clause or function or something? Like where the username field is they type <a href="mysite.com">Click my username to go to my site</a> and when ever there username is displayed it'll display it like this Click my username to go to my site and when ever they click it is goes to there site???
User avatar
a94060
Forum Regular
Posts: 543
Joined: Fri Feb 10, 2006 4:53 pm

Post by a94060 »

yes,i think this is what they mean. Or it may be some other serious commands like sending a query withthin the query (i dont know if its possible)
User avatar
No0b
Forum Commoner
Posts: 37
Joined: Tue Feb 07, 2006 6:17 pm

Post by No0b »

Can I just put a 25 char limit on the username fields???
Post Reply