Display as picture in PHP from MySQL?

[luxuri]

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.

[Pullo]

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:

• 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" />).

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]

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?

[Pullo]

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]

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?

[Pullo]

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]

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

Thanks for the first step

[Pullo]

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]

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

[Pullo]

Hi,

This is from your original post:

Is it possible if, for example a column in my database is called "point".

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

[luxuri]

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]

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.

Nevermind, there is no space between w and [ thanks

[Pullo]

So, does it work?

[luxuri]

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.

[Pullo]

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

[luxuri]

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

[Pullo]

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]

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.
1. id; which has ai because it raised by one per entry
2. food; int
3. env; int
4. serv; int
5. hyg; int
6. 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.

[Pullo]

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]

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.

[Pullo]

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

Other than that you can execute MySQL commands via PHPMyAdmin:
http://www.siteground.com/tutorials/...ysql_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.

[luxuri]

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.

[Pullo]

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).

[luxuri]

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?

[Pullo]

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?