Displaying mySQL data within a web page using PHP

PHP
1

Going to try and make this as short as possible. I haven’t dabbled in SQL since college, and need some refreshing. What I need to happen is relatively simple.

Users will visit the page below, fill out the form, and they are then redirected to a thank you page where I need to display the First Name, Last Name, Event Date, and # of tickets. (feel free to enter in some test data).

Pittsburgh Vintage Grand Prix 2011

The guts of the form were already in place and use CoffeeCup flash, which seems to use xml for the variables within the form, and php for the emailing of the form and writing to the DB.

Here is the code used to pull the flash form on the first page.

			<script type="text/javascript">var so = new SWFObject("pvgp.swf", "pvgp.xml", "500", "363", "7,0,0,0", "#ffffff");so.addParam("classid", "clsid:d27cdb6e-ae6d-11cf-96b8-444553540000");so.addParam("quality", "high");so.addParam("scale", "noscale");so.addParam("salign", "lt");so.addParam("wmode", "transparent");so.addParam("FlashVars", "xmlfile=pvgp.xml&w=500&h=363");so.write("CC5723836");</script>

Can someone please help me fill in the blanks below? I pulled this script from a google search, but need to fill in some blanks. (open source of course). There is an instance of phpMyadmin running on the ip address in question. (please see comments below)

<?php
#connect to MySQL
$conn=@mysql_connect("205.178.146.80","usernamehere","passwordhere") or die("Err:conn");
 
#select the specified database
$rs=@mysql_select_db("pvgp-2011", $conn) or die("Err:Db");
 
#create the query
$sql="select id,cat_name from table"; //This would be where I pull the First Name, Last Name, Date, and Tickets correct? I know the table name, but not sure what the cat_name is
 
#execute the query
$rs=mysql_query($sql,$conn);
 
#write the data
while( $row = mysql_fetch_array($rs) )
{
    echo("ID: ".$row["id"]);
    echo(" Print Data: ".$row["cat_name"]."<br>");
}
?>

Thanks so much ahead of time

2

look for a table which contains cats probably called “categories”.

then tell us the output of these 2 queries (ie paste these directly into your database - nothing to do with PHP)


DESCRIBE table
DESCRIBE categories

Where ‘table’ and ‘categories’ are the REAL table names.

3

Here is what i got when I did describe table. I am not sure what the categories are though? Thanks for your help cups!

300×300 14.3 KB

4

Lets forget cat_name then that seem to be some kind of place holder from an example you found …

Going by your original post - lets say you want just
id
First Name
Last Name
Phone Number

For the user whose id is 1


// connect to db as before ...

#create the query **
$sql="select 
`First Name` as first,
`Last Name` as last,
`Phone Number` as tel
from pvgp-2011 where id = 1"; 
 
#execute the query
$rs=mysql_query($sql, $conn);
 
#write the data
while( $row = mysql_fetch_array($rs) )
{
    echo '&lt;p&gt;ID: ' . $row["id"]) . '&lt;br /&gt;';
    echo 'Name: ' . $row['first'] . ' ' . $row['last'] . '&lt;br /&gt;' ;
    echo 'Tel:  ' . $row['tel'] '&lt;/p&gt;' ;
}
?&gt;

** here the field names such as First Name contain a space, which I am not sure is legal so I have quoted with backticks ` in the hope this works.

5

Cups. I think we are close. Here is the code I implemented… but I got this error.

Parse error: syntax error, unexpected ‘)’, expecting ‘,’ or ‘;’ in /data/18/2/103/153/2103153/user/2306916/htdocs/PVGP2011/thank-you.php on line 162

&lt;?php
#connect to MySQL
$conn=@mysql_connect("205.178.146.80","usernamehere","passwordhere") or die("Err:conn");

#select the specified database
$rs=@mysql_select_db("pvgp-2011", $conn) or die("Err:Db");


// connect to db as before ...

#create the query **
$sql="select
`First Name` as first,
`Last Name` as last,
`Phone Number` as tel
from pvgp-2011 where id = 1";

#execute the query
$rs=mysql_query($sql, $conn);

#write the data
while( $row = mysql_fetch_array($rs) )
{
    echo '&lt;p&gt;ID: ' . $row["id"]) . '&lt;br /&gt;'; // This is line 162 that created the error
    echo 'Name: ' . $row['first'] . ' ' . $row['last'] . '&lt;br /&gt;' ;
    echo 'Tel:  ' . $row['tel'] '&lt;/p&gt;' ;
}
?&gt;
6

There were 2 errors, you need to learn how to identify and fix these yourself from the clues in the error messages - you will be seeing a lot of them.

{
    echo '<p>ID: ' . $row["id"] . '<br />'; // This is line 162 that created the error
    echo 'Name: ' . $row['first'] . ' ' . $row['last'] . '<br />' ;
    echo 'Tel:  ' . $row['tel'] . '</p>' ;
}

Compare the code above with my original post and see if you can spot them both - if you can’t them retype them, char by char till you do.

7

Hmm after reading the error message I guess that was relatively self-explanatory :wink:

Thanks Cups!

Now getting the Err:Db to display on the thank you page, so making some progress.

8

Well if it is the line below, then it seems you have found a pretty fundamental error …

#select the specified database
$rs=@mysql_select_db("pvgp-2011", $conn) or die("Err:Db");

Remove that @ it is there to suppress errors, which is what you need to examine to find out what the error is…

Also, you could start monitoring in your mysql log files. I know this all sounds never-ending, but it will work in the end and you will be hopefully be armed with a few more tricks.

9

Thanks again cups. I will look into the log files. Where about may I find these files? Sorry I am pretty new to this.

removed the @ and got the same error, so I will def need to look into those log files.

10

Find your mysql install folder, and it should be in there somewhere mine are in this folder:

“C:\Program Files\MySQL\MySQL Server 5.0\data” and the file will be the last updated file, which will be / <your server name>.log

You should be reading the manual page for each line that features a PHP function [fphp]mysql_select_db[/fphp] uses as an example:


<?php

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('foo', $link);
if (!$db_selected) {
    die ('Can\\'t use foo : ' . mysql_error());
}
?>

I am sorry I am not giving you absolute working code on this, but tbh I don’t use mysql_* functions any longer …

11

No need to apologize. I appreciate all of your help, as this is all I have to go from right now.

C:\Program Files\MySQL\ <~~~This folder does not exist on my computer

I did a search for all log files on my computer, and the only one coming up as updated when I submit the form is…

C:\WINDOWS\SoftwareDistribution\DataStore\Logs

12

hmmm… just noticed the IP address here:


<?php
#connect to MySQL
$conn=@mysql_connect("205.178.146.80","usernamehere","passwordhere") or die("Err:conn");

Am I right in saying you do not have a mysql database on your own test machine, the one sitting in front of you?

Am I right in saying you are trying to connect to a server elsewhere on the internet?

13

You are right on both accounts sir.

14

Well, that is my fault for not noticing at the get go.

Lets try this, because you have understood that your script will only work from your LOCAL machine if you are connected to the internet?

Can you ping that server? [google]ping[/google]

Have you got PHP working on your LOCAL server, the one sitting in front of you - or are you trying to do this working remotely on a live server?

15

Yes my local machine is connected to the internet. Attached is the result I get when I ping 205.178.146.80

PHP is not installed on my local machine. I am working remotely on a live server. All the php/xml/html files are stored on the bobbyrahal.net server, and the DB is on 205.178.146.80. I know this is not the best case scenario but I unfortunately got handed this project half complete.

16

Actually, as you already connected to PMA (PHPMYAdmin) to get the describe dumps, I guess you DO have a connection and you can connect up ok.

The following only applies if you are developing and testing on a LOCAL machine.

So, can your LOCAL machine connect to the server at all?

Make a script with a really simple connection such as this, do not have any variables - ie hard code the actual user/pass combos.

connect_test.php


<?php

echo "SANITY CHECK<hr />';

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('foo', $link);
if (!$db_selected) {
    die ('Can\\'t use foo : ' . mysql_error());
}

phpinfo();
?>

replace ‘localhost’, ‘mysql_user’, ‘mysql_password’, ‘foo’ and ‘foo’ with the correct values.

Put this connect_test.php page in your home folder on your LOCAL machine and see what it returns when you navigate to:

http://localhost/connect_test.php
OR
http://127.0.0.1/connect_test.php

17

Cross-reply there, forget that last info.

So, you are working on LIVE files on a remote server.

Ok create a file as I described above, BUT BE PREPARED TO DELETE IT straight away as you will have hard coded username password details in it.

Does that connect to the database?

18

ps I’m out for a while, anyone wants to pick this up, do so please …

19

Okay so basically I will be putting the connect_test.php file in the home directory of bobbyrahal.net? ie: http://bobbyrahal.net/connect_test.php.

I think I understand everything from your previous post with the exception of the ‘foo’ variable… what do I put in place of ‘foo’?

20

Thank you for your help Cups. I know you are away for a while but I have until Tuesday to get this pieced together. I appreciate all the help you are giving me.

I implemented to code you suggested above, placed it onto http://bobbyrahal.net/connect_test.php, tried to run the file and got this error.

Parse error: syntax error, unexpected $end in /data/18/2/103/153/2103153/user/2306916/htdocs/connect_test.php on line 19

There is no code at line 19. I also deleted the file quickly as you suggest.