Display as picture in PHP from MySQL?

luxuri · November 13, 2012, 11:23am

Hi all.

I’m not sure if the title makes sense, but here is what I’m trying to do.

Is it possible if, for example a column in my database is called “point”. Inside, I would enter numbers from 1-5. Is it possible for PHP or the html to display instead of numbers 1-5, each number would display a different picture?

I’m not sure if that made sense, if it doesn’t please reply and I will tell more in detail. I can’t search in Google because of my lack of capability to word them correctly and find my answer.

this is for my school project,
thanks all.

James_Hibbard · November 13, 2012, 12:31pm

Hi there,

sure it’s possible!
What you would do is save your numbers in the database as normal.

Then, in your PHP script, you would:[LIST]
[]connect to your database
[]build a query along the lines of SELECT point FROM yourTable
[]store the result of the query in a variable
[]iterate over the result set using (for example) while and mysql_fetch_array
[]in your while loop, you could then do something like echo ("<img src="/path/to/images/folder/$row['point'].jpg />");
[]this would output an image tag, whose src attribute points to a jpg with the same name as the value stored in your database (e.g. <img src=“/images/5.jpg” />).
[/LIST]
Does that make sense?
You would essentially be using the values stored in point as file names.

If you need help implementing any of this, just let me know (but try and have a go yourself, first).

luxuri · November 13, 2012, 2:03pm

As much as how I’m tempted to do this, I simply do not get it I don’t get the “while” and things, sorry
I apologise if I’m a MySQL and PHP noob but I just started. However I appreciate your help and it seems really cool and I can’t wait for it to work… Can you help me give an example in a code?

James_Hibbard · November 13, 2012, 2:28pm

Yup, no problem.
Before we start:
Do you have a database which you can connect to and a PHP environment to test in?
Also, does your table with the column “points” exist?

luxuri · November 13, 2012, 2:30pm

Yes, I’ve recently purchased hosting from hostgator. I have a database to test it. Even not, I have MAMP on my computer where I can locally test it.
And sorry, I don’t know what’s points?

James_Hibbard · November 13, 2012, 2:40pm

Hi,

Sorry"points" should have been “point”, the column used to store the number you want to display as an image.

So, the first thing to do is check if you can connect to the database.
You can do that, like this:

<?php
$con = mysql_connect("localhost","yourName","yourPassword");
if (!$con)  {
  die('Could not connect: ' . mysql_error());
}
mysql_select_db("yourDatabase", $con);
echo "Connected to database. All good!";
mysql_close($con);
?>

Try that out and make sure that you see the message that says your connected to the DB.

luxuri · November 13, 2012, 2:50pm

Hey, I tried this code and it worked; it displayed Connected to database. All good!

Thanks for the first step

James_Hibbard · November 13, 2012, 3:19pm

Cool.
Now we need to select everything in the column “point” in your database table and store the result of this query in a variable.
We do this, like this:

$result = mysql_query("SELECT point FROM yourTable");

This returns a results set which we can loop over using while and mysql_fetch_array

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

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

mysql_close($con);
?>

I’ve got to duck out now for a bit, but this should give you something to experiment with.
Let me know how you get on.

luxuri · November 17, 2012, 10:53am

Hi, sorry for being so late. However I still dont get what is a point in the column you said. Can you please explain

James_Hibbard · November 17, 2012, 10:56am

Hi,

This is from your original post:

“point” is the name of the column, whose contents you wish to output to the screen.

luxuri · November 17, 2012, 11:12am

Oh, my bad. I feel really stupid now. I apologize for being so soft in the head.

I’m going to try your code above now - and I will reply with the results. Thank you

luxuri · November 17, 2012, 11:28am

[I]Hi, I’ve updated the php document - but it shows an error:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/eugechin/public_html/mysqltest.php on line 18

On line 18, it is echo “$row[‘food’]”; where food is my column.[/I]

Nevermind, there is no space between w and [ thanks

James_Hibbard · November 17, 2012, 11:47am

So, does it work?

luxuri · November 17, 2012, 11:50am

Hi - I am having trouble inserting data into my table right now, so I can’t test it out… I need to figure it out before it will work I think.

James_Hibbard · November 17, 2012, 11:51am

Yup, this is true.
You can also do this via PHP.
If you are interested I can show you how.

luxuri · November 17, 2012, 11:56am

Please do. If PHP is less troublesome, I’d love to learn about this and put it in my journal.

Right now, because of trouble putting information in, I dropped the table and tried to create one using PHP. However, when I visit the link it returns me with a message like this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’ env INT), service INT), hygiene INT), price INT), address VARCHAR(30), p’ at line 4

James_Hibbard · November 17, 2012, 11:57am

OK, let’s take a step back.
What is your database called?
What is the table called?
Which columns should be in the table (name and type)?

luxuri · November 17, 2012, 12:04pm

My database is called cafe
My table is called review
I don’t know if I’m doing this right, but there are 9 columns in the table.

id; which has ai because it raised by one per entry
food; int
env; int
serv; int
hyg; int
price; int
7 add; varchar(80)
8 phone; varchar(30)
9 text; varchar(300)

I actually do not know if this is how I should do it but I am taking step by steps.

James_Hibbard · November 17, 2012, 12:17pm

OK, to make the table review in the database cafe you could use the following code:

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

mysql_select_db("cafe", $con);
$sql = "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";

mysql_query($sql, $con);
mysql_close($con);

echo "The table has been created. All good!"
?>

Try that out.
Does that work?

luxuri · November 17, 2012, 12:27pm

Hi. When I run the php, it displays the successful message.
However, when I go back to phpmyadmin, it doesn’t display that table.

Perhaps there may be some confusion between the database. Im hosting with Hostgator and I create a database from their cPanel. Therefore, after I make a db, it will be called username_dbname. I tried entering

mysql_select_db(“username_dbname”, $con);
and also
mysql_select_db(“dbname”, $con);
and run the php, but it doesnt appear.