Retieving MySQL data with PHP

[rcouser]

I might sound really stupid here but I have just started learning PHP and MySQL. I have got to the stage where I can connect to the database, prepare SQL to retrieve data, submit the query and extract the first record as an array but only for one table. I don’t really know how to explain this but I’ll try my best.

For example I have a list of locations that I want to display on the same web page twice, once in the topnav and once in the page content. When I call <?php echo $row['location']; ?> in the topnav and try to call it on the page, only the topnav location shows up but if I copy the same PHP code and paste it below, renaming the variables $sql to $topnav and $result to $nav they both work.

The thing that confuses me is do I have to connected database, prepare SQL, submit query and extract record with different variables every time I pull data.

 

My second question is how you pull related information from two tables, say I have a table with a list of locations with a unique number called location_id and I have a table with a list of items with title, image and a link to the related location_id. I want to pull a list of items that come from a specific location. Below is example code that I have tried but hopefully someone can help me understand the process.

Kind Regards.

 

 

Example Code 1

// create a connection to MySQL

$conn = dbConnect('query');

 

// prepare SQL to retrieve data

$sql = "SELECT * FROM location;

 

// submit the query

$result = $conn->query($sql) or die(mysqli_error());

 

// extract the first record as an array

$row = $result->fetch_assoc();

 

Example Code 2

$sql = "SELECT title, image, location

FROM listing, location

WHERE listing.location_id = $location_id

AND listing.location_id = location.location_id";

 

[psychowolvesbane]

Well to answer your first question, once you extract the information from the database once you only need to assign the data to local variables that can be accessed anywhere on the same page as long as the database is accessed on the first occurrence.

 

So you have:

$Location = $row['location'];

$Field2 = $row['field2'];

...and so on.

 

Then just reference $Location anytime you want that information again afterwards.

 

Okay, moving on...

 

For multiple tables the query should look something like this:

 

$sql "SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = 'value1' AND table2.column2 ='value2' ";

 

you don't need to include the table1. and table2. reference in the SELECT and WHERE part but it helps you when it comes to reading bigger scripts.

[rcouser]

Sorry for the late reply by my internet has been down 

Thank for the reply. Got that all worked out, but now I've got another question for you.

I'm trying to create a registration form with in built php validation. The validation such as check every field is has data, email is valid etc all works fine, but once I come to inserting the date into the database

 

// insert details into database

$insert = "INSERT INTO sr_users (user_name, salt, user_pwd, first_name, family_name, user_email)

VALUES ('$username', $salt, '$password', $firstname, $lastname, $email)";

$result = $conn->query($insert) or die(mysqli_error($conn));

 

I get the following error

 

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 '@hotmail.com)' at line 2

[PhillNeedsHelp]

Hey try:

 

// insert details into database
$insert = "INSERT INTO sr_users (user_name, salt, user_pwd, first_name, family_name, user_email)
VALUES ('$username', '$salt', '$password', '$firstname', '$lastname', '$email')";
$result = $conn->query($insert) or die(mysqli_error($conn));

[rcouser]

Works a gem,cheers pal

Would you believe I checked everything for about 2hrs and just couldn't see that simple thing 

[rcouser]

Hello me again, I have just realised that the solution I used for looping data from the database hasn't work properly,only the first section of code is executed. Does anyone have a solution?

Thanks

// define number of items in navigation
define('COLS', 7);

// create database connection
$conn = dbConnect('query');
$sql = 'SELECT * FROM sr_location';
$result = @ $conn->query($sql);
if (!$result) {
header('Location: http://www.mysite.com/error.php');
exit;
}
// extract records as an array
$row = $result->fetch_assoc();
?>

 

<dl>
<!--This row needs to be repeated-->
<?php
// initialize cell counter outside loop
$pos = 0;
do {
?>
        <dd><a href="categories.php?location_id=<?php echo $row['location_id']; ?>"><?php echo $row['location']; ?></a></dd>
<?php
$row = $result->fetch_assoc();
// increment counter after next row extracted
$pos++;
// if at end of row and records remain, insert tags
if ($pos%COLS === 0 && is_array($row)) {
 echo '</dl><dl>';
 }
} while($row);  
 ?>
</dl>

 

 

<ul>
<?php do { ?>
<li><a href="categories.php?location_id=<?php echo $row['location_id']; ?>"><?php echo $row['location']; ?></a></li>					
<?php
$row = $result->fetch_assoc();
} while ($row);
?>
</ul>

[psychowolvesbane]

I've always done while($row = mysql_fetch_array($result)), try and see if that works instead of a do..while() loop.

[rcouser]

No that didn't work,when you remove the second section of code the third one works and when your remove third section of code the second one works, so that shows there nothing wrong with the loops. I'm really confused here.

[rcouser]

I've got it working anyway,had to create a new connection with different variable name.Seems to be working fine.Do you see any problems with doing it this way.

 

// define number of items in navigation
define('COLS', 7);
// create database connection
$conn = dbConnect('query');
$sql = 'SELECT * FROM sr_location';
$result = @ $conn->query($sql);
if (!$result) {
header('Location: http://www.startrecycling.org/error.php');
exit;
}
// extract records as an array
$row = $result->fetch_assoc();

// create database connection
$conn = dbConnect('query');
$sql2 = 'SELECT * FROM sr_location';
$result2 = @ $conn->query($sql2);
if (!$result2) {
header('Location: http://www.startrecycling.org/error.php');
exit;
}
// extract records as an array
$nav = $result2->fetch_assoc();
?>