Jump to content

Archived

This topic is now archived and is closed to further replies.

156418

Select MAX() Problem

Recommended Posts

In my sctipt, I'm taking some values which have been posted through from a form, and then adding them to the Database.
This part is working ok, and the values are being written, however I'm trying now to take the MAX Value of CustID and carry it though as CustID2 later on, once the use makes the selection from the EventDescription box (the drop down is working fine)

However my CustID2 and Password fields are blank. (i've made them as input boxes rather than hidden to check that they are working.
Event Description is the only value being posted.

[code]<?php
//we need to connect to the database so
require('db/db_connect.php');

// make the variables easy to access in our queries
$Title = $_POST["Title"];
$Initials = $_POST['Initials'];
$Surname = $_POST['Surname'];
$HouseNo = $_POST['HouseNo'];
$Road_Name = $_POST['Road_Name'];
$Town = $_POST['Town'];
$County = $_POST['County'];
$Postcode = $_POST['Postcode'];
$Country = $_POST['Country'];
$TelHome = $_POST['TelHome'];
$EmailAddress = $_POST['EmailAddress'];
$Password = $_POST['Password'];

//1) Assign Customer Number to new Customer, or find existing customer number
  $query = "INSERT INTO CustomerDetails (
            Title, Initials, Surname, HouseNo, RoadName, Town, County, Postcode, Country, TelHome, EmailAddress, Password)
            VALUES (
            '$Title',
            '$Initials',
            '$Surname',
            '$HouseNo',
            '$Road_Name',
            '$Town',
            '$County',
            '$Postcode',
            '$Country',
            '$TelHome',
            '$EmailAddress',
            '$Password')";
  $insert = mysql_query($query)
    or (mysql_error());
?>

<?php

$query = "SELECT Max(CustID) AS CustID2 FROM CustomerDetails Where EmailAddress = '"& EmailAddress &"'"

$results = mysql_query($query)
or die(mysql_error());

?>

<form name="form1" method="post" action="Stage4-Date.php">
     
        <?php

//we need to connect to the database so

require ( 'db/db_connect.php' );

$result = mysql_query ( 'SELECT * FROM Events WHERE EventID = 22;' ) or die ( 'Query Error: ' . mysql_error () );

if ( mysql_num_rows ( $result ) > 0 )
{
echo "<select name='EventDescription'>";

while ( $row = mysql_fetch_array ( $result, MYSQL_ASSOC ) )
{
echo "<option value='" . $row['EventDescription'] . "'>" . $row['EventDescription'] . "</a>";
}

echo '</select>';
}
else
{
echo 'can not create select box, no results found';
}

?>
      <input type="input" name="CustID" value="<?php echo $_POST['CustID2']; ?>">
        <input type="input" name="Password" value="<?php echo $_POST['Password']; ?>">
  <input type="submit" name="Submit" value="Submit"></form>[/code]

I've chopped out the HTML in the middle of the two php tags as it isnt necessary for the script, but can someone point out where the MAX query is failing and not displaying the data.

I'm not getting any error messages. Thanks for any help

Share this post


Link to post
Share on other sites
I cannot see that you are fetching the result from the MAX query named $results .....
You need to run e.g. mysql_fetch_array($results) - as you have it now you only run the mysql_query($results)

Share this post


Link to post
Share on other sites
I've cut my query down now so that its only doing 1 thing, but still cant seem to find where the error is coming from.

I'm getting

[quote]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 '' at line 1[/quote]

For the following:

[code]<?php
//we need to connect to the database so
require('db/db_connect.php');
?>

<?php

$query = "SELECT Max(CustID) AS CustID2 FROM CustomerDetails Where EmailAddress = '"& EmailAddress &"'";

$results = mysql_query($query)
or die(mysql_error());

?><?php

// Show only Name, Price and Image
while ($row = mysql_fetch_array($results)) {
  extract($row);
  echo $CustID2;
}

?>[/code]

The only part I cant work out of the syntax is correct is the part with EmailAddress, thats being posted in from a previous form, I know thats being posted ok, but cannot find if the code for the Query is correct.

Share this post


Link to post
Share on other sites
[code]
<?php
//we need to connect to the database so
require('db/db_connect.php');
?>

<?php

$query = "SELECT Max(CustID) AS CustID2 FROM CustomerDetails Where EmailAddress = '$EmailAddress '";

$results = mysql_query($query)
or die(mysql_error());

?><?php

// Show only Name, Price and Image
while ($row = mysql_fetch_array($results)) {
  extract($row);
  echo $CustID2;
}

?>
[/code]


or search

[code]
<?php
//we need to connect to the database so
require('db/db_connect.php');
?>

<?php

$query = "SELECT Max(CustID) AS CustID2 FROM CustomerDetails Where  like EmailAddress = '"%$EmailAddress%"'";

$results = mysql_query($query)
or die(mysql_error());

?><?php

// Show only Name, Price and Image
while ($row = mysql_fetch_array($results)) {
  extract($row);
  echo $CustID2;
}

?>
[/code]

Share this post


Link to post
Share on other sites
Brilliant thank you.

I had just noticed in my original code I was thinking MsSQL rather than MySQL

Share this post


Link to post
Share on other sites
[quote author=156418 link=topic=110726.msg448875#msg448875 date=1160319379]

I had just noticed in my original code I was thinking MsSQL rather than MySQL
[/quote]

The MySQL and MSSQL sql code would be the same in this instance. What you had was VB code instead of PHP

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.