PHP - Left Join Is Null Issue

$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?

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.

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?

This topic has been moved to MySQL Help.

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

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

Hello all   I have the need to join up two tables and display the output in a table. These tables are a 'Site Details' table and a 'Customer Details' table where the 'cust_id' (PK on the CD table and FK on the SD table) match.   The MySQL statement (within PHP) I have works a treat in as much as the join seems to do the trick and join the two up on the correct row:

<?php
if($letter == 'ALL')
{$query = mysqli_query($con,
SELECT site_details.site_id, site_details.sitename, site_details.sitecode, 
site_details.postcode,customer_details.cust_number, customer_details.cust_fname 
FROM site_details 
LEFT JOIN customer_details ON site_details.cust_id = customer_details.cust_id
ORDER BY sitename ASC);
}

Hi

I am new to PHP / mySQL although not to programming and am trying to work out how to deal with the following situation:

I have a form for a user to add a book to a database, along with the genres that the book belongs to. Each book can have a number of genres
.

There are 3 tables, books, genres and bg_xref  in the following formats
table: books
fields: id, title, author, date published

table: genres
fields: id, genre

table: bg_xref
fields: id, book_id, genre_id

I am now trying to build the form that allows the user to update an existing entry for a book. the genres are selectable from a multi-selection list which I am populating from all the possible values in the genres table.

Currently I have the following database selections:
mysql_select_db($database_conn, $conn1);
$query_rs_getBooks = "SELECT books.id, books.title, books.author, bg_xref.genre_id
         FROM books LEFT JOIN bg_xref ON books.id = bg_xref.book_id";
$rs_getBooks = mysql_query($query_rs_getBooks, $conn1) or die(mysql_error());
$row_rs_getBooks = mysql_fetch_assoc($rs_getBooks);

mysql_select_db($database_conn, $conn1);
$query_rs_getGenre = "SELECT * FROM book_genres ORDER BY genre ASC";
$rs_getGenre = mysql_query($query_rs_getGenre, $conn1) or die(mysql_error());
$row_rs_getGenre = mysql_fetch_assoc($rs_getGenre);

I have then created a form that the user uses to update the book, but am trying to work out how to prepopulate the selection list based on teh results of the join e.g.

<form method="POST" action="<?php echo $editFormAction; ?>" name="update_book">
<fieldset class="full">
<legend>Enter book details below</legend>
<table>
<tr><td>Title: </td><td><input type="text" size="50" value="<?php echo htmlentities($row_rs_getBooks['title'], ENT_COMPAT, 'iso-8859-1'); ?>" name="title"></td></tr>
<tr><td>Author: </td><td><input type="text" size="30" value="<?php echo htmlentities($row_rs_getBooks['author'], ENT_COMPAT, 'iso-8859-1'); ?>" name="author_surname"></td></tr>

<tr><td>Genre(s)</td><td><select name="genre" multiple size="4">
  <?php do {  ?>
    <?php if (($row_rs_getBooks['genre_id']) != $row_rs_getGenre['id']) { ?>
      <option value="<?php echo $row_rs_getGenre['id']?>"><?php echo $row_rs_getGenre['description']?></option>
   <?php } else { ?>
    <option value="<?php echo $row_rs_getGenre['id']?>" selected><?php echo $row_rs_getGenre['description']?></option>
    <?php }
        } while ($row_rs_getGenre = mysql_fetch_assoc($rs_getGenre));
   $rows = mysql_num_rows($rs_getGenre);
   if($rows > 0) {
      mysql_data_seek($rs_getGenre, 0);
      $row_rs_getGenre = mysql_fetch_assoc($rs_getGenre);
  }
?>
</select></td></tr>
</table>
</fieldset>
<input type="hidden" name="id" value="<?php echo $row_rs_getBooks['id']; ?>">
<input type="submit" value="Update book details">
<input type="hidden" name="MM_update" value="update_book">
</form>

Book id = 1 is classified against genres 1 and 5, so my SQL query returns 2 rows:
book.id = 1 + bg_xref.genre_id = 1
book.id = 1 + bg_xref.genre_id = 5

At the moment the form is generated for the first row of the results set with
<tr><td>Genre(s)</td><td><select name="genre" multiple size="4">
                 <option value="5">Humour</option>
             <option value="4">Non Fiction</option>
             <option value="2">Novel</option>
             <option value="3">Picture Books</option>
             <option value="1" selected>Poetry</option>
            
with a second entry in the results set  for book 1 and genre 5.

What I need to end up with is a second occurence of book 1 with both genres 1 and 5 set in the form.

Thanks

Stuart

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

It's been a while since I sat down to build some pages and teach myself php. So now that I've started back up, I'm at a loss for what I've done. I deleted a file, and have to rebuild from an old broken version:

I have a form that submits a query to the database, but the results pages is giving me this error:

Code: [Select]
Oops, my query failed. The query is:
SELECT COUNT 'descriptors'.* ,'plantae'.* FROM 'descriptors' LEFT JOIN 'plantae' ON ('descriptors'.'plant_id' = 'plantae'.'plant_name') WHERE 'leaf_shape' LIKE '%auriculate%' AND 'leaf_venation' LIKE '%%' AND 'leaf_margin' LIKE '%%'
The error is:
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 '.* ,'plantae'.* FROM ' at line 2
But I'm not seeing what the syntax error is.

Here's the code:
Code: [Select]
<?php 
require ('connection.php');

$display = 2;
// it's intentionally only 2 for the moment

if (isset($_GET['np'])) {
   $num_pages = $_GET['np'];
} else { 
$data = "SELECT COUNT
                        'descriptors'.*
                        ,'plantae'.*
                    FROM
                        'descriptors'
                    LEFT JOIN
                        'plantae' ON ('descriptors'.'plant_id' = 'plantae'.'plant_name')
                    WHERE
                        'leaf_shape' LIKE '%$s1%'
                        AND 'leaf_venation' LIKE '%$s3%'
                        AND 'leaf_margin' LIKE '%$s4%'";
$result = mysql_query ($data);

if (!$result) {
    die("Oops, my query failed.  The query is: <br>$data<br>The error is:<br>".mysql_error());
}

$row = mysql_fetch_array($result, MYSQL_NUM);

$num_records = $row[0];

if ($num_records > $display) {
   $num_pages = ceil ($num_records/$display);
} else {
$num_pages = 1;
}
}
if (isset($_GET['s'])) {
   $start = $_GET['s'];
} else {
    $start = 0;
}

if(isset($_POST[submitted])) {
// Now collect all info into $item variable
$shape = $_POST['s1'];
$color = $_POST['s2'];
$vein = $_POST['s3'];
$margin = $_POST['s4'];




// This will take all info from database where row tutorial is $item and collects it into $data variable 

$data = mysql_query("SELECT
                        'descriptors'.*
                        ,'plantae'.*
                    FROM
                        'descriptors'
                    LEFT JOIN
                        'plantae' ON ('descriptors'.'plant_id' = 'plantae'.'plant_name')
                    WHERE
                        'leaf_shape` LIKE '%$s1%'
                        AND 'leaf_venation' LIKE '%$s3%'
                        AND 'leaf_margin' LIKE '%$s4%'
                    ORDER BY 'plantae'.'scientific_name` ASC LIMIT $start, $display");



//chs added this in... 
echo '<table align="center" cellspacing="0" cellpading-"5">
<tr>
<td align="left"><b></b></td>
<td align="left"><b></b></td>
<td align="left"><b>Leaf margin</b></td>
<td align="left"><b>Leaf venation</b></td>
</tr>
';
//end something chs added in 

// This creates a loop which will repeat itself until there are no more rows to select from the database. We getting the field names and storing them in the $row variable. This makes it easier to echo each field.

while($row = mysql_fetch_array($data)){
echo '<tr>
<td align="left"> <a href="link.php">View plant</a> &nbsp; &nbsp;</td>
<td align="left"> <a href="link.php">unknown link</a>&nbsp; &nbsp;</td>
<td align="left">' . $row['scientific_name'] . '</td>
<td align="left">' . $row['common_name'] . '</td>
<td align="left">' . $row['leaf_shape'] . '</td>
</tr>';
}
echo '</table>';
// row 95
}
if ($num_pages > 1) {
echo '<br /><p>';
$current_page = ($start/$display) + 1;
// row 100
if ($current_page != 1) {
echo '<a href="leafsearch2a.php?s=' . ($start - $display) . '&np=;' . $num_pages . '">Previous</a> ';
}

for ($i = 1; $i <= $num_pages; $i++) {
if($i != $current_page) {
echo '<a href="leafsearch2a.php?s=' . (($display * ($i - 1))) . '$np=' . $num_pages . '">' . $i . '</a>';
} else {
echo $i . ' ';
}
}


if ($current_page != $num_pages) {
 echo '<a href="leafsearch2a.php?s=' . ($start + $display) . '$np=' . $num_pages . '"> Next</a>';
}
} 
//added curly
?>

Here is what I am trying to accomplish:

I have a students table with a studentID
I also have a notes table and a sched table with studentID

The sched table is working as planned where if the student is scheduled for more than one time he is displayed twice.
However if there are multiple notes per student the student is display for each individual note where I would like only the most current note to be displayed

Here is what the output is:

1- 10:30:00 - 10:50:00   student3 Three   FST   Teacher One   Special1 One   Writing Ratios   09/10
2- 10:30:00 - 10:50:00   student3 Three   FST   Teacher One   Special1 One   Needs to work on fractions and decimals   09/10
3- 13:00:00 - 14:00:00   student3 Three   FST   Teacher One   Special1 One   Writing Ratios   09/10
4- 13:00:00 - 14:00:00   student3 Three   FST   Teacher One   Special1 One   Needs to work on fractions and decimals   09/10

As you can see I have two notes and two schedule times for this student. what I want it to display is only line 1 & 3. which is the newest note in the system.

Below is my code that I am using. Any help would be greatly appreciated.


Code: [Select]
<?php 
$result = mysql_query("SELECT * FROM students 
  LEFT JOIN teachers ON students.teacherId = teachers.teacherId
  LEFT JOIN course ON students.courseId = course.courseId
  LEFT JOIN specialEd ON students.specialId = specialEd.specialId
  LEFT JOIN sched ON students.studentId = sched.studentId 
  ORDER BY start
  ");
echo "<table>";
while ($row = mysql_fetch_array($result)){
$id = "?id=" . $row['studentId'];
echo "<tr>";
echo "<td>" . $row['start'] . " - " . $row['stop'] . "</td>";
echo "<td>" . "<a href='student.php$id'>" . $row['fName'] . " " . $row['lName'] . "</td>";
echo "<td>" . $row['courseName'] . "</td>";
echo "<td>" . $row['teachers_fName'] . " " . $row['teachers_lName'] . "</td>";
echo "<td>" . $row['special_fName'] . " " . $row['special_lName'] . "</td>";
echo "<td>" . $row['note'] . "</td>";
echo "<td>" . date("m/d", strtotime($row['started'])) . "</td>";
echo "</tr>";
}
echo "</table>";
?>

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

I am trying to update the database with isset to set the value of the variable to either null or the value entered by the user. Currently, if no value has been entered null is being set as a string causing the date field to populate with zeros. How do I code it so that it populates or sets the value as null and not as string null?   Here's my code: (yes I know sql injections it's still in development  ) <?php