Display as picture in PHP from MySQL?

[luxuri]

I would much rather prefer a PHP script, I interpret that as a file where I can access and type in information. Similar to a form? This is because I'd like easier access to future additions. Thanks

[Pullo]

OK then, inMySQL you can insert data into a table in one of two ways:

1)

INSERT INTO tableName SET
  column1Name = column1Value,
  column2Name = column2Value,
  ⋮

2)

INSERT INTO tableName
  (column1Name, column2Name, …)
  VALUES (column1Value, column2Value, …)

This would translate to this for you:
1)

INSERT INTO review SET food = 1, price = 999, text = "A hotdog";

2)

INSERT INTO review (food, price, text) VALUES (1, 999, "A hotdog");

It doesn't matter which you use.

Now to do this from within a PHP script, we connect to the database, select the table we want to insert stuff into and then execute the MySQL statement:

<?php
$con = mysql_connect("localhost","user","password");
if (!$con)  {
  die('Could not connect: ' . mysql_error());
}
 
mysql_select_db("cafe", $con);
mysql_query("INSERT INTO review (food, price, text) VALUES (1, 999, 'A hotdog')");
echo "Data was inserted!";
mysql_close($con);
?>

This obviously uses method two.
Does this work for you?
Can you see the results from within PHPMyAdmin?

[luxuri]

Hi. I understand this and I will try this later(it's late at night and I'm really tired). However, is there a way to use a form instead? Such as, having something like $comment=$_POST['comment']; where comment would be the column in the table, where then you can add

$query=mysql_query("INSERT INTO comment (id,name,comment) VALUES ('','$name','$comment')

. Im not sure if this works for this, but I hope you understand what I'm trying to do?

I just looked at your reply and I see that example two from yours is similar to mine. Yet my example uses post method with a form and in your example, it is written directly in the file.

[Pullo]

That's also possible.
Sorry if I misunderstood you.

You'll be best off following a tutorial about how to do that.
This one seems ok at first glance: http://www.wallpaperama.com/forums/h...code-t868.html
Have a read of that and let me know if you get stuck.

[luxuri]

Thanks. The website seems simple enough to do, I'll start it tomorrow morning. I'll let you know my progress and problems; thank you

[luxuri]

Hi. I've recreated the script by following the tutorial, modified something and viola - it works. Visually, everything is entered correctly and the id with auto increment works. However I still have to properly test it.

Now, we should go back to topic and can you tell me how to display images when I put numbers in the database?

Many thanks.

[Pullo]

Now, we should go back to topic and can you tell me how to display images when I put numbers in the database?

I'm glad you got everything working
The next thing is to simply display the contents of a table column on the screen.

We can do this, like this (I'm using the column 'price' as an example):

<?php  
$con = mysql_connect("localhost","yourName","yourPassword");  
if (!$con)  {  
  die('Could not connect: ' . mysql_error());  
}  
mysql_select_db("cafe", $con);  

$result = mysql_query("SELECT price FROM review"); 
while($row = mysql_fetch_array($result))  { 
  echo "$row['price']<br />"; 
 } 

mysql_close($con);  
?>

Does this work for you?

[luxuri]

Hi

While displaying the columns, I've referred to a previous script I had and applied it to this current situation. It works and they display correctly. I'll try with your code when I get back home.

[Pullo]

It works and they display correctly. I'll try with your code when I get back home.

Good.
Now, it should only be a short jump to getting the output to display as a graphic.
Can you post the script you used and the first few lines of the output.

[luxuri]

Hi. This is the code which makes it display the columns:

$query=mysql_query("SELECT * FROM review ORDER BY id DESC");
while($rows=mysql_fetch_assoc($query))
{
$id=$rows['id'];
$name=$rows['name'];
$food=$rows['food'];
$env=$rows['env'];
$service=$rows['service'];
$hygiene=$rows['hygiene'];
$price=$rows['price'];
$address=$rows['address'];
$phone=$rows['phone'];
$review=$rows['review'];
$linkspam="<a href=\"validate.php?id=".$rows['id']."\">Report as spam</a>";
echo '<font color="red">Restaurant Name/font> ' . $name . '<br />' . '<br />' . '<font color="red">Food Rating/font> ' . $food . '<br/ ><br />'.'<font color="red">Environment Rating/font> ' . $env . '<br />' . '<br />' . '<font color="red">Service Rating/font> ' . $service . '<br />' . '<br />' . '<font color="red">Hygiene Rating/font> ' . $hygiene . '<br />' . '<br />' . '<font color="red">Average Price per Person/font> ' . $price . '<br />' . '<br />' . '<font color="red">Address/font> ' . $address . '<br />' . '<br />' . '<font color="red">Phone/font> ' . $phone . '<br />' . '<br />' . '<font color="red">Review/font> ' . $review . '<br />' . '<br />' .
'&nbsp' . '&nbsp' . $linkspam . '<br />' . '<br />' .

Then, this will be the result:

Restaurant Name: test in login

Food Rating: 1

Environment Rating: 1

Service Rating: 14

Hygiene Rating: 4

Average Price per Person: 44

Address: rtesoij

Phone: oidajsf

Review: Maximum 500 characters

[Pullo]

Hi,

So, that all seems to be working as it should.
Now, let's take "Food Rating" as an example and have it display an image depicting the figure one, instead of simply printing "1".
To do this, you will need an image of the figure one (obviously).
For test purposes, just download one from Google image search: http://www.google.com/search?q=1&aq=...HYrmtQa42oGwAw
Ok, now save the image as "1.jpg" somewhere in your website's folder, for example a sub-folder called "images" in the project's root.
This means that the path to the image would be "www.yourdomain.com/images/1.jpg", or put another way "/images/1.jpg"

Now, the PHP code:

<?php  
$con = mysql_connect("localhost","yourName","yourPassword");  
if (!$con)  {  
  die('Could not connect: ' . mysql_error());  
}  
mysql_select_db("cafe", $con);  
 
$result = mysql_query("SELECT food FROM review"); 
while($row = mysql_fetch_array($result))  { 
  echo "$row['food']: <img src="/images/$row['food'].jpg" /><br />"; 
} 
 
mysql_close($con);  
?>

For every entry in "food", PHP will output its value as the name of a jpg.
So for example, the first row will be <img src="/images/1.jpg" />

And that's it (although obviously, you'll need images for the other numbers).

[squeal]

A little off topic, and security issues aside, this bit...

{
$id=$rows['id'];
...
$review=$rows['review'];
 $linkspam="<a href=\"validate.php?id=".$rows['id']."\">Report as spam</a>";

... could be replace with this...

{
extract($rows);
 $linkspam="<a href=\"validate.php?id=$id\">Report as spam</a>";

[luxuri]

Hey Pullo, thank you! You make it seem so easy

So, if I want to add other images from othe columns, do I repeat the code

$result = mysql_query("SELECT food FROM review");
while($row = mysql_fetch_array($result)) {
echo "$row['food']: <img src="/images/$row['food'].jpg" /><br />";
}

But enter the 'food' with another from another column?

squeal, thanks I didn't really know what I was doing at that part. I'll replace the code as soon as I get back home.

[Pullo]

Hey Pullo, thank you! You make it seem so easy

No problem. I'm happy to help.

So, if I want to add other images from other columns, do I repeat the code But enter the 'food' with another from another column?

Exactly. Instead of the food column, you could reference any other column containing an integer.

Please note that this method works well for the numbers 0 - 9, however when you get above single digits, you'll be wanting to split the number apart, loop through them and display the digits separately.
For example, instead of having an image for "10" you would split "10" into a "1" and a "0" and display them side by side.
This has the added advantage of being able to display numbers of any length using just ten images.

Also, as squeal points out, there are a couple of things you could improve in your code.
For example the use of the font tag.

Don't do this: <font color="red">Restaurant Name</font>

Do this: .highlight{color:red;}
Then this: <span class="highlight">Restaurant Name</span>

Hope this helps

[luxuri]

Hi. I've now progressed to creating dynamic pages using the mysql tables and php. MY current progress is creating the title and i'm already stuck I (think) this is what you use to put in <title> </title, which is

<title><?php echo $page_title; ?></title>

, however how do I link the title to make it from the row you're seeing?

To put it in more clearer description, now I want to make the page so that whenever you visit, for example www.site.com/page.php?id=3, it will bring you to a page where the layout is already created however taking the row 3(id 3) and displaying the corresponding row in the site. I think I've done this similarly before, but I forgot them all and I have mistaken everything with everything else...

If that doesn't make sense please tell me so I can explain thanks

[Pullo]

Hi there,

I want to make the page so that whenever you visit, for example www.site.com/page.php?id=3, it will bring you to a page where the layout is already created however taking the row 3(id 3) and displaying the corresponding row in the site.

You can do this using the $_GET superglobal variable.
Here is an example to demonstrate the principle.
Make a new PHP file, name it index.php, then with your favourite text editor, enter:

<?php
echo 'Hello ' . htmlspecialchars($_GET["name"]) . '!';
?>

Now access the file in your browser thus: http://site.com/index.php?name=luxuri
See how it works?

You can read more about this here: http://www.php.net/manual/en/reserved.variables.get.php

I hope that helps.

[luxuri]

I tried it it works but is it possible to use the browser link, i.e. ?id=1, then to take the row 1 from mysql and display it from there? because im having trouble referring to my older files and comprehending them.

[Pullo]

Is it possible to use the browser link, i.e. ?id=1, then to take the row 1 from mysql and display it from there?

Sure,
however, before we get into that, we should take a moment to think about the implications of passing unfiltered user input to a database query.
Imagine if a malicious user entered ?id=1; DROP review-- as a query string.
Then your MySQL query would select the appropriate data set, then promptly delete the entire table.
This is known as SQL injection.

So, one way to avoid this is to use the PDO library for your database queries.
You can read more about it here: http://net.tutsplus.com/tutorials/ph...tabase-access/

Using this library, the code to select a row from the database (based on a $_GET variable) and output it to the screen would be like this:

<?php
$id = $_GET['id'];
 
// Connect to db
try
{
  $pdo = new PDO('mysql:host=_____;dbname=_____', 'user', 'password');
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->exec('SET NAMES "utf8"');
}
catch (PDOException $e)
{
  echo "Unable to connect to the database server!<br>" . $e;
  exit();
}
 
// The query
$sql= $pdo->prepare('SELECT * FROM review WHERE id = :id');
$sql->execute(array(':id' => $id));
 
// Do something with result
foreach ($sql as $row) {
  echo $row['text'];	
}
?>

As you can see, we have bound the user input to a pre-defined query, then executed it.
This goes a very long way to avoiding SQL injection attacks.

[luxuri]

Hi, thanks for the detailed explanation. I'll put this in my journal to record it

However, it doesn't work when I load up my website... and as an amateur, I cannot trouble shoot it.

Is it okay if I do this

 $id=$rows['id'];
     $dname=$rows['name'];
     $dcomment=$rows['comment'];
     $linkspam="<a href=\"validate.php?id=".$rows['id']."\">Report as spam</a>";
     echo '<font color="red">Name:</font>  ' . $dname . '<br />' . '<br />' . '<font color="red">Comments:</font>  ' . '<br />' . $dcomment . '&nbsp' . '&nbsp' . 

instead of

// Do something with result
foreach ($sql as $row) {
  echo $row['text'];    
} 

,

or will it have no difference?

[Pullo]

Hi,

It all depends how you are fetching data from the database.
As long as $row holds a valid dataset it doesn't matter how you access its attributes.

You can post your code (minus passwords) if you get stuck.

[luxuri]

Hi, I've looked at the site you showed me previously (tutsplus) and used their example to display my information from the database. However, it also displays some excessive numbers and letters. Currently, using your example, if I visit www.site.com/restaurant.php?id=1 , nothing will show in my content part.

Here's the code(without passwords):

<?php 
$hostname = "localhost"; 
$db_user = "user";
$db_password = ""
$database = "db"; 
$db_table = "review"; // leave this as is 

$db = mysql_connect($hostname, $db_user, $db_password); 
$page_title=$rows['name'];
$id=$rows['id'];
$dname=$rows['name'];
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>foodhouse|<?php echo $page_title; ?></title>
<link rel="icon" type="image/png" href="favicon.png" />
<link rel="stylesheet" href="style.css" />
<script type="text/javascript" src="js/jquery-1.7.min.js"> </script>
<script type="text/javascript" src="js/jquery-ui-1.8.12.min.js"> </script>
<script type='text/javascript' src='/js/lib/mootools-core-1.3-full-compat-nc.js'></script>
<script type="text/javascript">
function Toggle(id,close,open,ms){
 var obj=document.getElementById(id),o=Toggle['zxc'+id],to;
 if (!o&&obj){
  Toggle['zxc'+id]=o={
   obj:obj,
   now:0,
   ms:500,
   ud:true
  }
 }
 if (o){
  ms=typeof(ms)=='number'?ms:o.ms;
  obj.style.display='block';
  to=o.ud?open:close;
  clearTimeout(o.dly);
  animate(o,o.now,to,new Date(),ms*Math.abs((to-o.now)/open));
  o.ud=!o.ud;
 }
}

function animate(o,f,t,srt,mS){
 var oop=this,ms=new Date().getTime()-srt,now=(t-f)/mS*ms+f;
 if (isFinite(now)){
  o.now=Math.max(now,0);
  o.obj.style.height=o.now+'px';
 }
 if (ms<mS){
  o.dly=setTimeout(function(){ oop.animate(o,f,t,srt,mS); },10);
 }
 else {
  o.now=t;
  o.obj.style.height=o.now+'px';
  if (t==0){
   o.obj.style.display='none';
  }
 }
}
</script>
<script type="text/javascript">
animatedcollapse.addDiv('jason', 'optional_attribute_string')
//additional addDiv() call...
//additional addDiv() call...
animatedcollapse.init()
</script>
<link href='http://fonts.googleapis.com/css?family=PT+Sans' rel='stylesheet' type='text/css'>
</head>

<body>
<div id="contain">
<div id="header">
<div id="hwrapper">
		<a href="#" id="logo"><img src="flogo.png" alt="Logo" width="460px" height="140px"/></a>
  </div>
</div>

<div id="container">
	<div id="line">
</div>
<div id="navbar">
    <div id="nav">
    	<ul>
        	<li><a href="#">Home</a></li>
            <li><a href="#">List</a></li>
            <li><a href="#">Submit</a></li>
            <li><a href="#">About</a></li>
            <li><a href="#"><div class="button open-button" onMouseUp="Toggle('page-split-wrap',0,250,500);"><span style="font-style:italic; font-size:19px;">Restaurant Search</span></div></a>

</li>
        </ul>
    </div>
 
    <div id="body">
     <div id="page-split-wrap">
  <div class="page-split-head"> </div>
  <div class="page-split-content">
   <p class="inside">Content in here</p>
   <p class="inside">Content in here</p>
  </div>
 </div>
    <div id="wrapperb"> 
    <?php
$id = $_GET['id'];
 
// Connect to db
try
{
  $pdo = new PDO('mysql:host=localhost;dbname=db', 'user', 'pw');
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->exec('SET NAMES "utf8"');
}
catch (PDOException $e)
{
  echo "Unable to connect to the database server!<br>" . $e;
  exit();
}
 
// The query
$sql= $pdo->prepare('SELECT * FROM review WHERE id = :id');
$sql->execute(array(':id' => $id));
 
// Do something with result
foreach ($sql as $row) {
  echo $row['text'];	
}
?>
</div>
</body>
</html>

[Pullo]

Hi,

You forgot the semicolon after $db_password = "", but I'm guessing that's a typo, right?

Apart from that, I fear you might have misunderstood me.
You should only connect to your database once.

For the previous examples we were using mysql_connect
However, once you wanted to start feeding user input into database queries, you need to be considerably more vigilant and that's why I recommended the PDO library.
This should then replace using mysql_connect.

So, what you need to do is get rid of the code which uses mysql_connect.
Then move the PDO code to the top of your file.
Check that you can connect to the database and select a table using this new method.
Then make a short example to check that you can display records based on what you are receiving via $_GET.

If this all sounds a bit complicated I can help you step through it if you like.

[luxuri]

Hey,

I'm pretty sure it was a typo for that . Now, I apologize for misunderstanding you. Again, I have yet to grasp the concepts of these, please forgive me

I'll try to figure it out on my own. If I need further help, I'll reply here again. Thanks for the detailed explanation.
As always, thanks for your passionate help.

[Pullo]

Hey,

No problem, everyone needs to learn somewhere.

It might be a good idea to start a new thread if you have any further questions to using the PDO library, as we have strayed somewhat from the original topic of this post, which was how to make a figure stored in a database appear as an image.

[luxuri]

Okay. Thanks, though. I think I've got it working, as for now, I can get everything displaying properly, corresponding to the link.

Now, back to topic. Is it possible to display the image by means of entering the image url? For example, if I have a row called "one", then can I insert a link inside the row, such as http://www.site.com/one.png, and then display the row "one" by showing the image? I'm asking for now because I have to go for dinner, and I'd want to try it out as soon as I get home.