My code requires Max to be used but I am getting an sql syntax error when I run the 2nd query (as noted below). The code runs fine if the 2nd sql statement is not queried.
This is what the code is supposed to do :
a. Select all data from a Table which have a Count>1. Count is the name of a field.
b. From this selection, choose only those records where Company and Model are the same.
c. From these records, print only that one which has the highest Count.
d. The Table Enquiry lists all the offers I have made to a company for various models.
e. Obviously when I meet a client, I will discuss all the offers made and then type out the meeting info. for each.
f. However, the number of visits to the client is just 1 but this 1 will appear in each of the records as I update it.
g. My purpose is to just list the number of times I have visited each company.
My code and a sample of the output is given below.
I have mysql ver 3.2353 could this be a problem ?
Please advise. Thanks.
The output below is without running this sqlb. My requirement is to ensure that if I met Company A 4 times to discuss Models 1, 2 and 3 and the same Company A 2 times to discuss Models 2 and 4, then I must get
Company A : 4
Company A : 2
Currently , my output is as under which is wrong because it may mean that I met Company A 16 times instead of the actual 6.
Company A Model 1 : 4
Company A Model 2 : 6
Company A Model 3 : 4
Company A Model 4: 2
<? include("protect.php"); ?>
<? ini_set('error_reporting', E_ALL); ?>
<td> Select</td>
<td> ID </td>
<td> Customer</td>
<td> Model </td>
<td> Number of Visits </td>
</tr>
<?php
$year = $_POST["year"];
mysql_connect("localhost", $dbname, $dbpasswd )
or die ("Unable to connect to server.");
mysql_select_db($database)
or die ("Unable to select database.");
$sqla = "SELECT * FROM `Enquiries` WHERE ` userid`= '$userid' && ` Count` >=1 && (Month(`Date`) >= 1 && Year(`Date`) = $year) ORDER BY `Date` asc ";
$resulta = mysql_query($sqla) or die (mysql_error());
if ($myrowa = mysql_fetch_array($resulta)) {
do {
$model=$myrow["Model"];
$company = $myrow["Company"];
$metdate = $myrowa["Date"];
list($year, $month, $day) = explode('-', $metdate);
$sqlb= "SELECT MAX(`Count`) FROM `Enquiries` WHERE `Company`='$company' AND `Model` = $model AND `Date` = ' $metdate' GROUP BY `Company`";
$resultb= mysql_query($sqlb) or die (mysql_error());
$myrowb= mysql_fetch_row($resultb);
$count = $myrowb[0];
printf("
<tr>
<td> %s <td>
<td> %s <td>
<td> %s <td>
<td> %s <td>
</tr> ",
$myrowa["Enquiryid"],
$myrowa["Company"],
$myrowa["Model"],
$count );
} while ($myrowa = mysql_fetch_array($resulta));
} else {
echo "Sorry, no records were found!";
}
?>
</table>
<p>
