Page 1 of 2

Getting info from the database...

Posted: Thu Feb 09, 2006 1:23 pm
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:

Posted: Thu Feb 09, 2006 1:37 pm
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.

Posted: Thu Feb 09, 2006 2:02 pm
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???

Posted: Thu Feb 09, 2006 2:07 pm
by josh
$result would be the mysqlresult of your query, in this case the data you asked it to select

Posted: Thu Feb 09, 2006 2:26 pm
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";
??????????????????????????????????????????????????????????????????????????????????????

Posted: Thu Feb 09, 2006 3:22 pm
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

Posted: Thu Feb 09, 2006 3:32 pm
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

Posted: Thu Feb 09, 2006 3:38 pm
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");

Posted: Thu Feb 09, 2006 3:51 pm
by No0b
Why must the mysql_real_escape_string() function be used when send iformation from the query?

Posted: Thu Feb 09, 2006 3:53 pm
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"

Posted: Thu Feb 09, 2006 3:54 pm
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.

Posted: Thu Feb 09, 2006 3:59 pm
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

Posted: Sat Feb 11, 2006 12:36 pm
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???

Posted: Sat Feb 11, 2006 4:56 pm
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)

Posted: Sat Feb 11, 2006 5:13 pm
by No0b
Can I just put a 25 char limit on the username fields???