PHP/SQL Database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
hendy
Forum Newbie
Posts: 24
Joined: Sat Nov 29, 2003 6:35 am
Location: UK

PHP/SQL Database

Post by hendy »

Bellow is a Section of an assignment i have completed, could anyone suggest a different way of writing the code or if indeed there is any other way of writing the code? I hate Uni, it sucks, but the thigns we do to get money...!!! Seriously, any help would be greatly appreciated....

2. Write a MySQL query that will insert the following address into the
table ”addresses” assuming an active connection to the database ”personal”.
Mickey Mouse, 1 Disney Parade, Disneyland, USA.

3. (3%) Write a script that will connect to the database and table above, ready
for further use; (Assume the database is on the server localhost, and
that the username and password are both simply “test”).

4. (3%) Augment the script to perform a SELECT query to fetch all address
entries ordered by the name column in alphabetical order.

5. (3%) Augment the script to show the fetch entries, with each entry in a table
of its own.



2.-----------------------------------------------------------------------------
INSERT into personal (name, address1, town, country) values (‘Mickey Mouse’, ‘1 Disney Parade’, ‘Disneyland’, ‘USA’);

3.---------------------------------------------------------------------------
<?php
//set variables for database access:
$host = “localhost”;
$user = “test”;
$pass = “test”;
$dbname = “personal”;
$tblname = “addresses”;

$conn = mysql_connect ($host, $user, $pass);
?>

4.-------------------------------------------------------------------------
<?php
//set variables for database access:
$host = “localhost”;
$user = “test”;
$pass = “test”;
$dbname = “personal”;
$tblname = “addresses”;

$conn = mysql_connect ($host, $user, $pass);
$query = “SELECT * FROM $tblname ORDER BY name”;

$result = mysql_db_query($dbname, $query, $conn);
mysql_close ($conn);
?>

5.------------------------------------------------------------------------
<?php
//set variables for database access:
$host = “localhost”;
$user = “test”;
$pass = “test”;
$dbname = “personal”;
$tblname = “addresses”;

$conn = mysql_connect ($host, $user, $pass);
$query = “SELECT * FROM $tblname ORDER BY name”;

$result = mysql_db_query($dbname, $query, $conn);

//loop through records and output into table
while ($table = mysql_fetch_array ($query)){
print (“<table border=1 width =\”75%\” cellspacing=2 cellpadding=2 align=centre> \n”);
print (“<tr> \n”)
print (“<td>$table[name]</td> \n”)
print (“</tr> \n”)
print (“<tr> \n”)
print (“<td>$table[address1]<br>$table[address2]<br>$table[town] $table[postcode]<br>$table[country]</td> \n”)
print (“</tr> \n”)
print (“<tr> \n”)
print (“<td>$table[record_id]</td> \n”)
print (“</tr> \n”)
print (“</table> \n”)
}
mysql_close ($conn);
?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

answer 2 is wrong.

Code: Select all

INSERT INTO `addresses` (`name`, `address1`, `town`, `country`) VALUES ( 'Mickey Mouse', '1 Disney Parade', 'Disneyland', 'USA' )
instead of using mysql_db_query, you could select the database using [php_man]mysql_select_db[/php_man](). Then use [php_man]mysql_query[/php_man]().

generally, there's little to no need to call mysql_close, as the connection is automatically closed when the script terminates.

oh and please use

Code: Select all

tags when posting code.
hendy
Forum Newbie
Posts: 24
Joined: Sat Nov 29, 2003 6:35 am
Location: UK

Post by hendy »

Thanks feyd, but what are php tags (probably the dummest question but i gotta ask)
hendy
Forum Newbie
Posts: 24
Joined: Sat Nov 29, 2003 6:35 am
Location: UK

Post by hendy »

In your opinion what way should Question 2 look like?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

my answer to question 2, is inside my post.

Code: Select all

allow us to put code inside nice highlighted blocks like this:

Code: Select all

<?php
//set variables for database access:
$host = "localhost";
$user = "test";
$pass = "test";
$dbname = "personal";
$tblname = "addresses";

$conn = mysql_connect ($host, $user, $pass);
$query = "SELECT * FROM $tblname ORDER BY name";

$result = mysql_db_query($dbname, $query, $conn);

//loop through records and output into table
while ($table = mysql_fetch_array ($query)){
print ("<table border=1 width ="75%" cellspacing=2 cellpadding=2 align=centre> \n");
print ("<tr> \n");
print ("<td>$table[name]</td> \n");
print ("</tr> \n");
print ("<tr> \n");
print ("<td>$table[address1]<br>$table[address2]<br>$table[town] $table[postcode]<br>$table[country]</td> \n");
print ("</tr> \n");
print ("<tr> \n");
print ("<td>$table[record_id]</td> \n");
print ("</tr> \n");
print ("</table> \n");
}
mysql_close ($conn);
?>
just noticed your answer to 5 would have parse errors as well.
Last edited by feyd on Sun Aug 01, 2004 12:01 pm, edited 1 time in total.
hendy
Forum Newbie
Posts: 24
Joined: Sat Nov 29, 2003 6:35 am
Location: UK

Post by hendy »

I thought i did, sorry about that, where in question 5 did you notice the errors??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you're missing several semicolons after each of the print calls.
hendy
Forum Newbie
Posts: 24
Joined: Sat Nov 29, 2003 6:35 am
Location: UK

Post by hendy »

Thanks, as you can probably tell my PHP skills are somewhat limited, i really appreciate your help.

Regards

Hendy
Post Reply