How can I output my MySQL data?

[DigitalSky]

Okay, I'm pretty new to PHP and MySQL but I've been reading a lot of tutorials and learning quickly. One thing I am not sure of how to do yet is output data form my database on a page in my site.

I have a database setup with three fields. The first field is an 'id' field, the second is a 'price' field, and the third is a 'product' field. Basically what this is is a price list that holds the id, product, and product price.

I want to be able to echo the price for any given product via PHP from the MySQL database. The way I want to grab each price though is via the 'id'.

So, as an example I want to grab the row with the id of 5 and echo the price of that row but not the product name.

From what I understand I have to SELECT the row first and then turn the results into a variable, then I have to echo the variable via PHP, is this correct?

Could anyone give me some example code for this that I can play around with? I am really stuck on this one!

[jatar_k]

Welcome to WebmasterWorld DigitalSky,

Maybe try this thread from the library [webmasterworld.com]
[webmasterworld.com...]


>> I have to SELECT the row first and then turn the results into a variable, then I have to echo the variable via PHP, is this correct?

yes, pretty much and the thread above has the code

[Matthew1980]

Hi there DigitalSky,

Welcome to the forum ;-p

Yes you pretty much have it covered, I think you just need to understand the syntax and your away..

the code would be something like this:-

Change the detail to suite your database and connection info..

<?php

//start error checking, comment out if you don't want this
//Just a good method of debugging, but remove when you go public if you leave it in ;-p
error_reporting(E_ALL);

//setup connection

$conn = mysql_connect(" your Hostname","your Username","your password") or die(mysql_error());

//select a DB
mysql_select_db("your database", $conn) or die(mysql_error());

//Setup query
$dataFromTableQuery = "SELECT * FROM `the table name` WHERE `id` = 'ID that you want' ";

//send query
$dataFromTable = mysql_query($dataFromTableQuery, $conn) or die(mysql_error());

//Create page while data is fetch via a while loop

//any include files you may want, place before and after the while ;-p

while($result = mysql_fetch_object($dataFromTable))
{
//Break out of php for easier formatting and less strain on CPU (I stress this is just my preference)
?>
<p>Product ID:<?php echo $result->id;?>Product:<?php echo $result->product;?>&nbsp;Price: £<?php echo $result->price;?></p>
<?php
//data output finished close while loop
}

//Rest of your script

Hopefully you can see the general building blocks of data retrieval there. I put everything to echo out there, but delete what you don't want.

Typed on the fly so hopefully Ok,

[EDIT] Jatar_K is a quicker typist than me lol!
Have fun anyway!

Hope that helps,

Cheers,
MRb

[DigitalSky]

Matthew,

Thanks a million! Your response helped me a TON! I'm going to study your code and perhaps that will give me more of a feeling for the syntax and how things work. Like I said, I understand basics, I just didn't know the correct syntax so your response really helped me!

jatar_k,

Thanks for your response as well. That link you sent me too is very informative and greatly appreciated!

[DigitalSky]

Now the question is if I have multiple product prices that I want to echo the price for on a single page, how would I do that in the code and have it be able to differentiate from the different IDs when I echo the variable?

Instinctively I want to just copy and paste this line...

$dataFromTableQuery = "SELECT * FROM `the price_list` WHERE `id` = '1' ";
$dataFromTableQuery = "SELECT * FROM `the price_list` WHERE `id` = '2' ";
$dataFromTableQuery = "SELECT * FROM `the price_list` WHERE `id` = '3' ";
$dataFromTableQuery = "SELECT * FROM `the price_list` WHERE `id` = '4' ";

But it seems that might be a problem because if you try to echo the price using this line <?php echo $result->price;?> it's probably not going to know which ID you want to echo that price from.

[Readie]

You can do this with a PHP loop:

$sql = 'SELECT * FROM `the price_list` WHERE `id` >= 1 ORDER BY `id` ASC LIMIT 5';
// The above will show the 5 entrys with the lowest ID greater than or equal to 1
$result = mysql_query($sql);
$rows = mysql_num_rows($result);

for($i = 0; $i < $rows; $i++) {

/* The above sets $i as 0, increases it by one every time the loop is ran, and keeps going until it is the same as $rows */

$someVariable = mysql_result($result, $i, "columnName");
/* Note the $i variable for the second function argument */

echo $someVariable;
}

Hope this gets you there :)

Mike

[Matthew1980]

Hi there DigitalSky,

You will see from the posts as people put, as there is more than one way to go about doing something with PHP/Mysql.

As you learn, you will develop your own style and that's where the fun is IMHO The code that I posted is just my preferred method today, I probably will do something different tomorrow ;-p

Readie's example is another way of doing the same thing, just specifying a column name, whereas I asked for (*) everything, then I could select what I wanted from the returned array, just a matter of preference..

Have fun with the project anyway.

Cheers,

[EDIT] Pardon my ignorance to this, I didn't realise as you could have spaces in table names, I always use _ to connect words up, force of habit from C programming ;-p

MRb

[DigitalSky]

Readie,

So technically you could just keep using echo $someVariable; and it would increase the by one each time which in turn would list all of the prices at least until the loop stopped. Is that correct?

[Readie]

Until it runs out of rows to echo, yes.

>> Matt

I believe spaces are the only time you actually need to use the `` around the table/column name.

I always use the underscore too though, I've always disliked spaces

[edit]

Right, reading Jatar_k's comment perhaps it only works with column names

[edited by: Readie at 8:38 pm (utc) on Mar 19, 2010]

[jatar_k]

>> spaces in table names

can and should are different, change them to have underscores, not spaces

this works
SELECT * FROM `the price_list` WHERE `id` = '1'
this doesn't
SELECT * FROM the price_list WHERE `id` = '1'

and backticks aren't required

[Matthew1980]

Hi there jatar_k,

Right-O. WRT backticks, I always use them, just as a preferred method of formatting in a sql query statement :)

Cheers,
MRb

[DigitalSky]

Okay so I'm getting close to accomplishing what I want but now I'm having this issue. When I try update my price list via an HTML form I'm having a problem.

I've got two text fields on my HTML form. One of the text fields name is 'HS324Patrol' and the other is HS324Command'. When I submit the form the method is set to post and it sends the form to a file I have called 'update.php'.

The contents of that file look like this...

<?
 // Connect to the MySQL database
$username="username";
$password="password";
$database="prinfrared_price_list";

$HS324Patrol=$_POST['HS324Patrol'];
$HS324Command=$_POST['HS324Command'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "UPDATE price_list SET price = $HS324Patrol WHERE id = 0;";
$query = "UPDATE price_list SET price = $HS324Command WHERE id = 1;";

mysql_query($query);

mysql_close();

?>

It works for updating the first table row (meaning the first price) but it doesn't update the second price at all.

My table is laid out so that I have a id column and a price column. That way each price has a different ID.

[Readie]

For non-integers you need to add quotations around the value you are inserting. So this:

"UPDATE price_list SET price = $HS324Command WHERE id = 1;";

Becomes this:

"UPDATE price_list SET price = '$HS324Command' WHERE id = 1;";

-----

I also strongly suggest you change this:

$HS324Patrol=$_POST['HS324Patrol'];
$HS324Command=$_POST['HS324Command'];

To this

$HS324Patrol = mysql_real_escape_string($_POST['HS324Patrol']);
$HS324Command = mysql_real_escape_string($_POST['HS324Command']);

To protect yourself from what is called an injection attack. If you don't do this, someone could enter something like

a';DROP DATABASE prinfrared_price_list;

And nuke your entire database.

[Matthew1980]

Hi there DigitalSky,

[EDIT] Readie beat me to it, I can't help being slow at typing ;-p

couple of points:

1) Full tags are prefereable <?php as not all servers have short tags enabled

2)Format the query correctly (the backticks are optional, that's just my preference :)):-

$query = "UPDATE `price_list` SET `price` = '".$HS324Patrol."' WHERE `id` = '0' ";
$query = "UPDATE `price_list` SET `price` = '".$HS324Command."' WHERE `id` = '1' ";

3)Why do you need to suppress the error messages '@' remove this, the more info you can get on what's going wrong the better.

4)Check that the $_POST values are set and being sent, ie check typos and use print_r(); to see what's in the arrays once you submit the form, trace the data in other words ;-p

5)mysql_connect("localhost",$username,$password); Try the quotes to, not too sure if that's a problem, just a good way to get into a habit of good coding practice.

6)mysql_connect("localhost",$username,$password) or die(mysql_error()); give the connection a way of telling you if it connects to the server.

Hope this helps you in some way.

Cheers,

MRb

[Readie]

*Celebrates winning the typing race with over-the-top whooping etc.*

Back on topic:

mysql_connect("localhost",$username,$password) or die(mysql_error());

Only use the mysql_error() when setting up/testing/de-bugging. Don't leave it there when the site goes live as it's a bit of a security liability.

[DigitalSky]

Well now when you change

$HS324Patrol=$_POST['HS324Patrol'];

to

$HS324Patrol = mysql_real_escape_string($_POST['HS324Patrol']);

Nothing gets inserted in the database at all.

The complete code now looks like this...

<?
 // Connect to the MySQL database
$username="username";
$password="password";
$database="prinfrared_price_list";

$HS324Patrol = mysql_real_escape_string($_POST['HS324Patrol']);
$HS324Command = mysql_real_escape_string($_POST['HS324Command']);

mysql_connect("localhost",$username,$password);
mysql_select_db($database) or die( "Unable to select database");

$query = "UPDATE price_list SET price = '$HS324Patrol' WHERE id = 0;";
$query = "UPDATE price_list SET price = '$HS324Command' WHERE id = 1;";

mysql_query($query);

mysql_close();

?>

[Readie]

Hmm, I'm not sure on this as I like using single quotes and I don't know the effect single quotes within double quotes has... Try this:

$query = "UPDATE price_list SET price = '$HS324Patrol' WHERE id = 0;";
$query = "UPDATE price_list SET price = '$HS324Command' WHERE id = 1;"; 

To :-

$query = 'UPDATE price_list SET price = "' . $HS324Patrol . '" WHERE id = 0;';
$query = 'UPDATE price_list SET price = "' . $HS324Command . '" WHERE id = 1;'; 

[Readie]

Also this:

$query = "UPDATE price_list SET price = '$HS324Patrol' WHERE id = 0;";
$query = "UPDATE price_list SET price = '$HS324Command' WHERE id = 1;";

mysql_query($query); 

Is only putting

UPDATE price_list SET price = '$HS324Command' WHERE id = 1;

Into effect. The first query is being completely overwritten.

[DigitalSky]

Okay well I guess that's my real question than. If my code is only overwriting the first query I'm wondering how I prevent it from doing that.

[Anyango]

If my code is only overwriting the first query I'm wondering how I prevent it from doing that.

Simply, execute both those queries, your current code executes only 1 query.

Try this

$query = "UPDATE price_list SET price = '$HS324Patrol' WHERE id = 0;";

mysql_query($query);

$query = "UPDATE price_list SET price = '$HS324Command' WHERE id = 1;";

mysql_query($query);

[Matthew1980]

Lol - Both me and Readie should have notice that!

I concur with Readie too with regards to this:-

$HS324Patrol = mysql_real_escape_string(strip_tags($_POST['HS324Patrol']));
$HS324Command = mysql_real_escape_string(strip_tags($_POST['HS324Command']));

This just makes the data that is submitted from the form to the query free from any dangerous code that might harm your database & website.

Just in case of the two variables are cancelling each other out, change the second vars name so that there are two distinct queries:-

$QueryOne = "UPDATE `price_list` SET `price` = '".$HS324Patrol."' WHERE `id` = '0' ";
$QueryTwo = "UPDATE `price_list` SET `price` = '".$HS324Command."' WHERE `id` = '1' ";

$sent = mysql_query($QueryOne);
if($sent){
mysql_query($QueryTwo);
}else{
echo "Problem sending info";//theoretically would get to this clause..
}

This just makes sure that if the first query is sent, send the next one too! Still can't believe as we both missed that, lol!

That will do the trick ;-p

Cheers,
MRb

[Readie]

Personally I prefer to do something like this Matt, imo it makes for cleaner code:

mysql_query($sql1);

if(!mysql_error()) {
mysql_query($sql2);
} else {
// Error
}

[DigitalSky]

$QueryOne = "UPDATE `price_list` SET `price` = '".$HS324Patrol."' WHERE `id` = '0' "; 
$QueryTwo = "UPDATE `price_list` SET `price` = '".$HS324Command."' WHERE `id` = '1' "; 

$sent = mysql_query($QueryOne); 
if($sent){ 
mysql_query($QueryTwo); 
}else{ 
echo "Problem sending info";//theoretically would get to this clause.. 
} 

If I use that code, how would I modify it so that there could be a third, fourth, fifth, etc. query?

I tried adding additional querys to it but it only works with the first two and I'm sure this is because I'm modifying the code incorrectly.

[Readie]

Try doing this:

$query_one = ...
$query_two = ...
$query_three = ...
$query_four = ...

mysql_query($query_one);
if(!mysql_error()) {
mysql_query($query_two);
if(!mysql_error()) {
mysql_query($query_three);
if(!mysql_error()) {
mysql_query($query_four);
if(!mysql_error()) {
// Confirmation
} else {
// Part error, 3 parts confirmation
}
} else {
// Part error, 2 parts confirmation
}
} else {
// Part error, part confirmation
}
} else {
// Error
}

[DigitalSky]

Readie,

Thanks a million, that worked perfectly and it makes sense to me.

Now the real bran buster which I can't seem to figure out (and if I remember correctly you guys were unable to figure it out as well?) is this...

When I use this code...

$HS324Patrol = mysql_real_escape_string(strip_tags($_POST['HS324Patrol'])); 
$HS324Command = mysql_real_escape_string(strip_tags($_POST['HS324Command'])); 

It doesn't insert anything into the database except blank values.

When I use this code...

$HS324Patrol24=$_POST['HS324Patrol24']; 
$HS324Command24=$_POST['HS324Command24']; 

It inserts the values of those text fields into the database perfectly.

Any ideas on what could be going on here? I want to protect myself from possible injection attacks so I really want to figure out why that code won't work but the other code will.

[Matthew1980]

Hi there DigitalSky,

What will the data usually contained that's in these two variables? As the two functions there:-

mysql_real_escape_string(); will Escape special characters in a string for use in a SQL statement (excluding % and _ which according to the php.net are wildcards in mysql).

strip_tags(); Strips HTML and PHP tags from a string.

So if the vars contain anything like this, that's the reason. But, I doubt you are doing this!

I suggest that you try (instead of strip_tags()) use htmlentities(); This just converts html data into 'safe' chars for use in the mysql database, at least that's how I have understood this to function ;-p

Firstly, just try mysql_real_escape_string() this makes the sql safe at least. But I'm still curios to see what the var's contain, I suspect that its a £ or $ and 00.00 in that sort of format, as it is for the price field in your DB.

Cheers,
MRb

[DigitalSky]

Matthew,

All of the vars will be a price but the price only contains a comma. So all of the vars would be in this format... 5,999

Other than the comma that is it.

So do you think it's the comma that's causing the error? If that's the case so you think htmlentities(); might solve the problem?

[Matthew1980]

hi there DigitalSky,

How did you set up the table to handle this data? Ie: did you specify varchar, text, int etc, as I think that having the comma there may be to blame, personally I think as you may have to str_replace the , with &#8218; (I think that's the correct one ;-p) going into the db and str_replace the other way around when your calling it from the db to form...

Try htmlentities though, It may well work, but I think as str_replace may be the way to go, it is also meant to increase the sql efficiency, whether it does or not I'm not sure...

Cheers,
MRb

[DigitalSky]

Matthew,

Well, we solved the problem (or you did I should say!) htmentities(); works just great in place of strip_tags(). Now the form submits just fine with a comma in the price and I'm now safe from injection attacks!

So, I learned a whole lot from this thread and I thank all of you for your help. I have a lot to learn but this was a good start!

[Matthew1980]

Hi there DigitalSky,

Phew! I'm glad as this is now working as you wanted, sometimes it pays to have a play with a few different functions see how they differ in action. I should think as your pretty safe though now, so long as you have the mysql_real_escape_string() still there you db will be better protected against malicious code injection attempts.

There was a post a while back by one of the senior members of the forum detailing security: [webmasterworld.com ] Have a read & get the PDF that the original link is referring to, it's a great read IMHO.

Anyway, have fun with the rest of the project.

Cheers,
MRb