PHP - How Can I Echo Only The Newest Database Record. (mysql Left Join)

I have a MySQL database with each record of a person who has registered for an event, I am displaying the information on a web page for a user, but he wants to be able to print out all the records in alphabetical order by last name, first name later on so he will have a hard copy of each person who has registered at the table when they arrive.  How can I write each record to a Txt file that he can print out later that will be formated with the record contents along with each fields definition (Ex. Last Name - Smith, First Name - John, etc)?

This portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization)

users: id / username / password / realname / access_level
users_access_level: access_level / access_name

What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries.

Example row for users: 1234 / tmac / password / tmac / 99
Example row for users_access_level: 99 / Admin

Using the examples above, I would want the output to appear as such:

Username: Access Name: Tmac Admin
I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction.

The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself.

Thanks for reading!

I am trying to set up a item entry page form.png:

Upon submission it shows unsuccessful even though I have checked the fields on mysql table and seem to be good am I missing something?

Code: [Select]
<form action="" method="post" enctype="multipart/form-data" name="Product_Entry">
<TABLE>
<TR>
<TD>Product ID</TD><TD><input name="SKU_ProductID" value="<?php if (isset($_post['SKU_ProductID'])) echo $_POST['SKU_ProductID']; ?>" type="text" size="11" maxlength="11" /></TD>
</TR>
<TR>
<TD>Merchant SKU ID</TD><TD><input name="SKU_MerchSKUID" value="<?php if (isset($_post['SKU_MerchSKUID'])) echo $_POST['SKU_MerchSKUID']; ?>" type="text" size="18" maxlength="30" /></TD>
</TR>
<TR>
<TD>Game Title</TD><TD><input name="Game_Title" value="<?php if (isset($_post['Game_Title'])) echo $_POST['Game_Title']; ?>" type="text" size="40" maxlength="40" /></TD>
</TR>
<TR>
<TD>Platform</TD><TD><input name="Platform" value="<?php if (isset($_post['Platform'])) echo $_POST['Platform']; ?>" type="text" size="20" maxlength="20" /></TD>
</TR>
<TR>
<TD>Genre</TD><TD><input name="Genre" value="<?php if (isset($_post['Genre'])) echo $_POST['Genre']; ?>" type="text" size="11" maxlength="11" /></TD>
</TR>
<TR>
<TD>Weight</TD><TD><input name="Weight" value="<?php if (isset($_post['Weight'])) echo $_POST['Weight']; ?>" type="text" size="7" maxlength="7" /></TD>
</TR>
<TR>
<TD>Supplier</TD><TD><input name="Supplier" Value="<?php if (isset($_post['Supplier'])) echo $_POST['Supplier']; ?>" type="text" size="25" maxlength="25" /></TD>
</TR>
<TR>
<TD>Suppliers Price</TD><TD><input name="Supplier_Price" value="<?php if (isset($_post['Supplier_Price'])) echo $_POST['Supplier_Price']; ?>" type="text" size="10" maxlength="12" /></TD>
</TR>
<TR>
<TD>Cashback</TD><TD><input name="Cashback" value="<?php if (isset($_post['Cashback'])) echo $_POST['Cashback']; ?>" type="text" size="6" maxlength="8" /></TD>
</TR>
<TR>
<TD>Cashback Amount</TD><TD><input name="Cashback_Amount" value="<?php if (isset($_post['Cashback_Amount'])) echo $_POST['Cashback_Amount']; ?>" type="text" size="7" maxlength="11" /></TD>
</TR>
<TR>
<TD><input type="hidden" name="submitted" value="true"/></TD><TD><input name="Submit" type="submit" value="Add_Product" /></TD>
</TR></form></TABLE>

<?php # Product Entry

$page_title = 'Product Entry';

if (isset($_POST['Submit'])) {

$errors = array();

if (empty($_POST['SKU_ProductID'])) {
$errors[] = 'Please enter Product ID.';
} else {
$sid = trim($_POST['SKU_ProductID']);
}

if (empty($_POST['SKU_MerchSKUID'])) {
$errors[] = 'Please enter Merchant SKU.';
} else {
$mid = trim($_POST['SKU_MerchSKUID']);
}

if (empty($_POST['Game_Title'])) {
$errors[] = 'Please enter Game Title.';
} else {
$gt = trim($_POST['Game_Title']);
}

if (empty($_POST['Platform'])) {
$errors[] = 'Please enter Platform.';
} else {
$pl = trim($_POST['Platform']);
}

if (empty($_POST['Genre'])) {
$errors[] = 'Please enter Genre.';
} else {
$ge = trim($_POST['Genre']);
}

if (empty($_POST['Weight'])) {
$errors[] = 'Please enter Weight.';
} else {
$we = trim($_POST['Weight']);
}

if (empty($_POST['Supplier'])) {
$errors[] = 'Please enter Supplier.';
} else {
$sup = trim($_POST['Supplier']);
}

if (empty($_POST['Supplier_Price'])) {
$errors[] = 'Please enter Supplier Price.';
} else {
$sp = trim($_POST['Supplier_Price']);
}

if (empty($_POST['Cashback'])) {
$errors[] = 'Please enter Cashback %.';
} else {
$cb = trim($_POST['Cashback']);
}

if (empty($_POST['Cashback_Amount'])) {
$errors[] = 'Please enter Cashback Amount.';
} else {
$cba = trim($_POST['Cashback_Amount']);
}

if (empty($errors)) {

require_once ('connect.php');

[b]$q = "INSERT INTO `Products` (`SKU_ProductID`, `SKU_MerchSKUID`, `Game_Title`, `Platform`, `Genre`, `Weight`, `Supplier`, `Supplier_Price`, `Cashback`, `Cashback_Amount`) VALUES ('$sid', '$mid', '$gt', '$pl', '$ge', '$we', '$sup', '$sp', '$cb', '$cba')";
$r = @mysql_query ($dbc, $q);

if ($r) { // If it ran OK.

// Print a message:
echo '<h1>Thank you!</h1>
<p>Product Inserted!</p><p><br /></p>';

} else { // If it did not run OK.

// Public message:
echo '<h1>System Error</h1>
<p class="error">You could not be registered due to a system error. We apologize for any inconvenience.</p>'; 

// Debugging message:
echo '<p>' . mysqli_error($dbc) . '<br /><br />Query: ' . $q . '</p>';

} // End of if ($r) IF.

mysqli_close($dbc); // Close the database connection.[/b]
// Include the footer and quit the script:
include ('includes/footer.html'); 
exit();

} else {

echo '<H1>Error!</H1>
<p class="error">The Following error(s) occurred:<br />';

foreach ($errors as $msg) {

echo " - $msg<br />\n";
}

echo '</p><p>Please try again.</p><p><br /></p>';

}

}

?>
If there is anything else needed let me know

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=308855.0

I am using the following sql code to display a stream of statuses, I have the username/profile picture/status/likes working but I am now stuck on comments.

SELECT s.*, u.*, c.*, COUNT(l.likes_location_id) AS likeCount
FROM stream AS s
LEFT JOIN users as u ON (u.users_username = s.stream_username)
LEFT JOIN comments AS c ON ( c.comments_location_id = s.stream_id )
LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id )
 GROUP BY s.stream_id
ORDER BY s.stream_id DESC LIMIT 50

If a status has multiple comments the results only shows the first comment that was made. How can I get it to include all of them?   Also, how would I include this in the PHP loop, without having another loop?

Thanks,
Edited by slj90, 06 January 2015 - 08:56 PM.

When I have more than one table i don't know how to echo out a field if it is the same in both fields.  here's my code and I want to echo out the id from the people table:


$query =  "SELECT * FROM people INNER JOIN ratings ON people.id = ratings.rater_id AND ratings.event_id = '$eventid' AND ratings.complete = 'n' "; 

$result = mysql_query($query);
while ($row=mysql_fetch_assoc($result)){
echo $row['people.id'];
}


I tried  $row['people.id'] but it was blank. 

Alright this is going to be a doozy:

I have 3 tables: Collectibles, CollectiblesMethods and CollectionsCollectibles

Collectibles contains: ID, Photo, Collectible, Value, Resale, Rarity
CollectiblesMethods contains: ID, Collectible, Method
CollectionsCollectibles contains: ID, Collectible, Collection, Amount

Needless to say, this is a many to many relationship model. (many methods to one collectible, many collectibles to one collection).

The page collectibles.php is contained inside collspec.php

collspec.php shows information relating to a specific collection called by GET.
collectibles.php shows all collectibles belonging to the same specfic collection in collpec.php, thus why it is contained inside it.

I want collectibles.php to output the following:
Photo, Collectible, Amount, Resale, Rarity, Method

I already have collectibles.php outputting:
Photo, Collectible, Amount, Resale, Rarity

Method is proving to be difficult. I can either get it to show the word None for every collectible with this code:
<table width="99%">
<tr>
<th colspan="6"><h2>Collectibles</h2></th>
</tr>
<tr>
<td>Photo</td>
<td>Name</td>
<td># Needed</td>
<td>Sells For</td>
<td>Rarity</td>
<td>Obtained</td>
</tr>
<?php 
$sql2 = "SELECT * FROM CollectionsCollectibles LEFT JOIN Collectibles ON CollectionsCollectibles.Collectible = Collectibles.Collectible WHERE Collection = '" . mysql_real_escape_string($item) . "'";
$collectible = mysql_query($sql2);
if (trim($row['Collectible']) != '') 
while($row2 = mysql_fetch_array($collectible)){
?>
<tr>
<td width="14%" valign="top"><img src="<?php echo "$img{$row2['Photo']}"; ?>"></td>
<td><?php echo $row2['Collectible']; ?></td>
<td><?php echo $row2['Amount']; ?></td>
<td><?php echo $row2['Sell']; ?></td>
<td><?php echo $row2['Rarity']; ?></td>
<td>
<?php
$sql3 = "SELECT CollectiblesMethods.Method FROM CollectiblesMethods WHERE Collectible = '" . $row2['Collectible'] . "'";
$obtain = mysql_query($sql3);
if (trim($row['Method']) != '') 
while($row3 = mysql_fetch_array($obtain)){
?>
<tr>
<td><?php echo $row3['Method']; ?></td>
</tr>
<?php
} else 
  echo 'None';
?>
</td>
<?php
} else 
  echo 'None';
?>
</tr>
</table>

or I can get it to show the Methods (of which certain collectibles have more than one method) using this code but with a catch; it repeats the photo, name, value and resale if there is more than one method for a given collectible:
<table width="99%">
<tr>
<th colspan="6"><h2>Collectibles</h2></th>
</tr>
<tr>
<td>Photo</td>
<td>Name</td>
<td># Needed</td>
<td>Sells For</td>
<td>Rarity</td>
<td>Obtained</td>
</tr>
<?php 
$sql2 = "SELECT * FROM CollectionsCollectibles LEFT JOIN Collectibles ON CollectionsCollectibles.Collectible = Collectibles.Collectible LEFT JOIN CollectiblesMethods ON Collectibles.Collectible = CollectiblesMethods.Collectible WHERE Collection = '" . mysql_real_escape_string($item) . "'";
$collectible = mysql_query($sql2);
if (trim($row['Collectible']) != '') 
while($row2 = mysql_fetch_array($collectible)){
?>
<tr>
<td width="14%" valign="top"><img src="<?php echo "$img{$row2['Photo']}"; ?>"></td>
<td><?php echo $row2['Collectible']; ?></td>
<td><?php echo $row2['Amount']; ?></td>
<td><?php echo $row2['Sell']; ?></td>
<td><?php echo $row2['Rarity']; ?></td>
<td><?php echo $row2['Method']; ?></td>
<?php
} else 
  echo 'None';
?>
</tr>
</table>

So Let's say I have a collection called Toys. This is how it outputs using my above codes:

1st Code example:
__________________________________________
|Photo|Name |Amount|Sells For   |Rarity       |Obtained|
|Pic1  |Marble| x5        | 20 cents | Common | None     |
|Pic2  |Train   | x1       | 5 $          | Rare       |  None    |

2nd Code example:
__________________________________________
|Photo|Name |Amount|Sells For   |Rarity       |Obtained|
|Pic1  |Marble| x5        | 20 cents | Common | Barter   |
|Pic1  |Marble| x5        | 20 cents | Common | Trade    |
|Pic2  |Train   | x1       | 5 $          | Rare       |  Gift      |

How it should output:
__________________________________________
|Photo|Name |Amount|Sells For   |Rarity       |Obtained|
|Pic1  |Marble| x5        | 20 cents | Common | Barter   |
|        |          |             |               |               | Trade    |
|Pic2  |Train   | x1       | 5 $          | Rare       |  Gift      |

As you can see, I want it to output all methods obtained in the same box, increasing the rowspan as necessary.

I don't want it to output None (since this is not true) as in the first example.
I don't want it to repeat the same information simply to add a line for the next method as in the second example.

Can someone help me figure out what I'm doing wrong?

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=352028.0

$compQ = "SELECT companies.companyid, companies.companyname, companies.companylogo, companies.companyoccupation, companies.industry, eQuestions.capitalrequested FROM companies LEFT JOIN eQuestions ON companies.companyid = eQuestions.companyid";

This is not displaying data correctly.  I'm assuming eQuestions.capitalrequested is not in the correct spot?

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=343994.0

I need to set up pagination. I've seen a tutorial online, and in a book I have. But they're both for basic queries. 

I've got a LEFT JOIN. The basic script doesn't seem to work. Is there a different way it has to be done for joins?

I need to echo the "reward.id"


Code: [Select]


$sql = "select
reward.id, reward.location, reward.industry, reward.title, reward.stamp,
industry.id, industry.label, industry.location
FROM reward
LEFT JOIN industry ON industry.id = reward.industry
where (industry.label = '".$_GET['ind']."' and industry.location = '".$_GET['loc']."')";

$result = mysql_query($sql) or die(mysql_error());

while($info = mysql_fetch_assoc($result)){
             echo $info['HOW TO I PRINT reward.id'];
}free($result );


Any thoughts?

I am trying to write some code to display the visits to my site each week in batches of 13 weeks (1 quarter). I am getting the Week No and the number of hits, but the actual date, which is in a separate table is not being displayed. I have tried LEFT JOIN and INNER Join and even RIGHT JOIN (this only displays the Date). I can't see where I am going wrong! Here is the code:

<?
include('connect_visits.php');
doDB7();
$limitStart = $_POST['QUARTER'] - 13;
$Visit_data="SELECT Visits.ID, Visits.WVisits, Visits.WCom, WeekNo.WNo, WeekNo.WCom
        FROM Visits 
        LEFT JOIN WeekNo
            ON Visits.ID = WeekNo.WCom
        ORDER BY Visits.ID
	 LIMIT {$limitStart}, 13";
$Visit_data_res = mysqli_query($mysqli, $Visit_data) or die(mysqli_error($mysqli));
$display_block = "
<table width=\"20%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"white\" >
	<tr>
	<th>Week No</th>
	<th>Week Commencing</th>
	<th>Visits</th>
		</tr>";	
while ($C_info = mysqli_fetch_array($Visit_data_res)) {
		$Cid = $C_info['ID'];
		$Visits = ($C_info['WVisits']);
		$WeekNo = ($C_info['WCom']);
//add to display
$display_block .= "
<tr>
<td width=\"1%\" valign=\"top\">".$Cid."<br/></td>
<td width=\"8%\" valign=\"top\">".$WeekNo."<br/></td>
<td width=\"5%\" valign=\"top\">".$Visits."<br/></td>
</td></tr>";
}
mysqli_free_result($Visit_data_res);
?>

Where am I going wrong?
Edited by rocky48, 29 December 2014 - 12:53 PM.

What's wrong with the statement below? I'm not getting any results, but I know for a fact that there should be results. What I'm trying to do is this: Take ALL id's from table member, then compare it with table member_images and display ONLY the id's that DOES NOT exist in member_images

Code: [Select]
$sel_mem="SELECT id FROM member AS members LEFT JOIN member_images AS images ON members.id = images.id WHERE members.id IS NULL LIMIT 5";

Hi there i have 3 tables joined together and am outputting the results ok. Heres what the results look like:

Fleet Name: Home One
Detected: No
Ship Name: Slave 1 Bobafett
Hull: 3 / 2

Fleet Name: Home Two
Detected: Yes
Ship Name: Imperial Star Destroyer
Hull: 2 / 4

Fleet Name: Home Two
Detected: Yes
Ship Name: Executer
Hull: 10 / 10

The 3 tables join together by the following:
Planet = parsed url parameter
Fleets = fleets on the current planet
Ships = current ships in the fleets

Its all working fine except for one thing, the Fleet Name is repeated for each occurance of a ship. I want to make it so that Fleet Name only occurs once for each fleet so it would look something like:

Fleet Name: Home One
Detected: No
Ship Name: Slave 1 Bobafett
Hull: 3 / 2

Fleet Name: Home Two
Ship Name: Imperial Star Destroyer
Detected: Yes
Hull: 2 / 4

Ship Name: Executer
Detected: Yes
Hull: 10 / 10

Is this possible???

Heres the code in full:
Code: [Select]
<?php require_once('Connections/swb.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$colname_Planet = "-1";
if (isset($_GET['recordID'])) {
  $colname_Planet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_swb, $swb);
$query_Planet = sprintf("SELECT * FROM planet WHERE PlanetName = %s", GetSQLValueString($colname_Planet, "text"));
$Planet = mysql_query($query_Planet, $swb) or die(mysql_error());
$row_Planet = mysql_fetch_assoc($Planet);
$totalRows_Planet = mysql_num_rows($Planet);


$colname_Fleet = "-1";
if (isset($_GET['recordID'])) {
  $colname_Fleet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_swb, $swb);
$query_Fleet = sprintf("SELECT p.PlanetName, p.PlayerName, f.FleetName, f.Detected, s.ShipName, s.HealthA, s.HealthB FROM Planet p
LEFT JOIN Fleet f ON (p.PlanetName = f.PlanetName)
LEFT JOIN Ships s ON (f.FleetName = s.FleetName)
WHERE p.PlanetName = '$colname_Planet'");
$Fleet = mysql_query($query_Fleet, $swb) or die(mysql_error());
$row_Fleet = mysql_fetch_assoc($Fleet);
$totalRows_Fleet = mysql_num_rows($Fleet);
?>
<head>
<title>Untitled Document</title>
<style type="text/css">
<!--
body {
background-color: #FFFFFF;
}
-->
</style></head>

<body>

<table width="60%" border="0">
  <tr>
    <td width="10%">Planet:</td>
    <td width="14%"><?php echo $row_Planet['PlanetName']; ?></td>
    <td width="7%" rowspan="8">&nbsp;</td>
    <td colspan="2" rowspan="8">

<?php while ($row_Fleet = mysql_fetch_assoc($Fleet)) {
     
echo '<table width="400" border="0"><tr><td width="200">';
echo 'Fleet Name: ';     
 echo'</td><td>';
echo $row_Fleet['FleetName']; 
 echo'</td></tr><tr><td>';
 echo 'Detected: ';
 echo'</td><td>';
 if ($row_Fleet['Detected'] == '1'){
 echo 'Yes';}
 else echo 'No';
 echo'</td></tr><tr><td>';
 echo 'Ship Name: ';
 echo'</td>';
        echo'<td>';
echo $row_Fleet['ShipName'];
echo'</td></tr><tr><td>';
echo 'Hull: ';
echo'</td><td>';
echo $row_Fleet['HealthA'];
 echo ' / ';
 echo $row_Fleet['HealthB']; 
 echo '</td></tr><tr><td></td></tr>'; 
    echo'</table>'; } ?>
  <tr>
    <td>Faction:</td>
    <td><?php echo $row_Planet['PlayerName']; ?></td>
  </tr>
  <tr>
    <td>Infastructure</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>Class 1: </td>
    <td><?php echo $row_Planet['Class1']; ?></td>
  <tr>
    <td>Class 2: </td>
    <td><?php echo $row_Planet['Class2']; ?></td>
    </tr>
  <tr>
    <td>Class 3: </td>
    <td><?php echo $row_Planet['Class3']; ?></td>
  </tr>
  <tr>
    <td>Class 4: </td>
    <td><?php echo $row_Planet['Class4']; ?></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>  <a href="index.php target="main2.php"">Back</a></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td width="43%">&nbsp;</td>
    <td width="26%">&nbsp;</td>
  </tr>
</table>
</body>
</html>
<?php
mysql_free_result($Planet);
mysql_free_result($Fleet);
?>

Thanks