Display as picture in PHP from MySQL?

Here is an article on connecting to a Hostagtor database remotely.
Does that help?
http://support.hostgator.com/articles/cpanel/how-to-connect-to-the-mysql-database

Other than that you can execute MySQL commands via PHPMyAdmin:
http://www.siteground.com/tutorials/phpmyadmin/phpmyadmin_mysql_query.htm
So you could just enter the MySQL part of what I posted:

CREATE TABLE review(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  food INT,
  env INT,
  serv INT,
  hyg INT,
  price INT,
  add VARCHAR(80),
  phone VARCHAR(30),
  text VARCHAR(300)
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB

I’ve got to duck out for a bit now, but post back here and I’ll get back to you in a bit.
We have to be able to connect to your database for any of this to work.

Hi. I’ve tried executing commands via PHPMyAdmin. Yet, it gives me an error.
However I’ve once again manually created a table and it shows up already. The next step is to put information in it, which I have trouble. Then, to retrieve the data which is the topic of the forum.

I appreciate your help, I’ll reply when you give me further steps.

Once again, thank you.

So, back with you.
So, tell me what is working, what isn’t and what I can help you with.
(I’ve lost the thread of this discussion a little).

Well, I’ve already set up the table as said. What I need to do now is to be able to put information in. I tried an old document and modifythem, but it doesnt work. Is there a way, like a form, so if I submit to the appropriate text boxes, they will go to the appropriate column. Then, I can retrieve information from the column and make a page out of it?

Yes, you could do that, but you can also enter data via PHPMyAdmin or via a PHP script directly.
Which do you prefer?

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

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

INSERT INTO tableName SET
  column1Name = column1Value,
  column2Name = column2Value,
  ⋮
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";
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?

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.

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/how-to-insert-data-into-mysql-db-using-form-in-php-database-enter-code-t868.html
Have a read of that and let me know if you get stuck.

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

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.

I’m glad you got everything working :slight_smile:
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?

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.

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.

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

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=f&sugexp=chrome,mod%3D18&um=1&hl=en&ie=UTF-8&tbm=isch&biw=1920&bih=1056&sei=mC2pUIntHYrmtQa42oGwAw
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).

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>";

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

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 :smiley: I didn’t really know what I was doing at that part. I’ll replace the code as soon as I get back home.

No problem. I’m happy to help.

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

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 :slight_smile: 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