Index not Primary Key

[morrisproject]

I have changed a column in my database from the primary Key to Index, as i need to allow the same reference in the column for multiple entries with different dates against them, but now i need to adjust my queries. how do i do this?

[smantscheff]

Which queries do you need to adjust? In which queries do you refer explicitly to the primary key?

[morrisproject]

i have the following code, conveyor_number was the primary key but now its index.

$id = $_POST;
mysql_select_db($database_Database_Connection, $Database_Connection);
$query_Recordset1 = "SELECT * FROM conveyor_number WHERE conveyor_number.`Conveyor Number` = '$id'";
$Recordset1 = mysql_query($query_Recordset1, $Database_Connection) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
print_r($row_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);?>

<?php
$id = $_POST;
# the query will become:
$query_Recordset1 = "SELECT * FROM conveyor_number WHERE conveyor_number.`Conveyor Number` = $id";
?>

[smantscheff]

The query does not change, regardless of the index structure of the table. As long as you do not explicitly refer to the index in your query, the index structure does not matter to the query statement. The index helps the database engine to answer queries more efficiently, but this does not affect the wording of your queries.
The index structure does matter, of course, to the performance and query optimisation, but in view of your code I doubt that you will tackle those problems anytime soon.

[morrisproject]

ok thank you, but now i seem to get an undefined variable :id, error on this line: $query_Recordset1 = "SELECT * FROM conveyor_number WHERE conveyor_number.`Conveyor Number` = '$id'";

so i was assuming it was to do with me changing it to an index?

[smantscheff]

You are confusing PHP errors with mysql messages.
Your line of code

$query_Recordset1 = "SELECT * FROM conveyor_number WHERE conveyor_number.`Conveyor Number` = '$id'

is just a PHP operation and has only semantical links to MySQL but does not execute any MySQL code. $id may be undefined because $_POST is undefined which means that maybe you got a variable name wrong in your HTML form.

[morrisproject]

i used this variable before and it was working, now it doesnt let me add any new records to my database

[smantscheff]

Show the output of

SHOW CREATE TABLE conveyor_number

[morrisproject]

Im sorry how do you mean?

I am not getting that error anymore, its now Undefined index: conveyor Number and Asset_Type.....

and also a warning on this line: sprintf():too few arguements in ....line... query was empty.

I get these errors once my form is filled in and i try to save the record.

[smantscheff]

The errors which you mention are PHP errors, not MySQL errors.
Show the code where they occur.
How do you insert a record? Show the code.
There is a difference between conveyor Number and Conveyor Number . PHP array indexes are case sensitive.

[morrisproject]

This is the page i am using to insert the record.

New.php

<?php require_once('Connections/Database_Connection.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $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;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form2")) {
  $insertSQL = sprintf("INSERT INTO conveyor_number (`Conveyor Number`, `Date`, `Asset Type`, `Motor Temperature`, `Motor Frame` ) VALUES (%s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['Conveyor Number'], "text"),
                       GetSQLValueString($_POST['Asset_Type'], "int"),
                       GetSQLValueString($_POST['Motor_Temperature'], "int"),
                       GetSQLValueString($_POST['Motor_Frame'], "int"));
                      GetSQLValueString($_POST['Date'], "date"));

  mysql_select_db($database_Database_Connection, $Database_Connection);
  $Result1 = mysql_query($insertSQL, $Database_Connection) or die(mysql_error());

  $insertGoTo = "record_saved.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}


$id = $_POST['id']; 
mysql_select_db($database_Database_Connection, $Database_Connection);
$query_Recordset1 = "SELECT * FROM conveyor_number WHERE conveyor_number.`Conveyor Number` = '$id'";
$Recordset1 = mysql_query($query_Recordset1, $Database_Connection) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
print_r($row_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);?>

 <?php
        $id = $_POST['id']; 
        # the query will become:
        $query_Recordset1 = "SELECT * FROM conveyor_number WHERE conveyor_number.`Conveyor Number` = $id";
?>

<?php
$Date=date ("d-m-y H:i");
mysql_query("INSERT into conveyor_number (Date) values(NOW(), CURTIME ()");?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>

<form action="<?php echo $editFormAction; ?>" method="POST" name="form2" id="form2">
  <table align="center">
  <tr valign="baseline">
      <td nowrap="nowrap" align="right">Date:</td>
      <td><input type="text" name="Date" value="<?php echo $Date ; ?>" disabled/></td>
      </td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Conveyor Number:</td>
      <td><input type="text" name="Conveyor Number" value="<?php echo $id; ?>" disabled /> </td>

      </td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Asset Type:</td>
      <td><input type="text" name="Asset_Type" value="<?php echo $row_Recordset1['Asset Type']; ?>"disabled /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Motor Temperature:</td>
      <td><input type="text" name="Motor_Temperature" value="" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Motor Frame:</td>
      <td><input type="text" name="Motor_Frame" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">&nbsp;</td>
      <td><input name="save" type="submit" id="save" value="Save" onclick="location.href='layout.php'"/>
        <p><label><input type="submit"  value="History" /></label><input name="id" type="hidden" value="<?php echo $id; ?>" />
</p></td>
    </tr>
  </table>

  <input type="hidden" name="MM_insert" value="form2" />
</form>
<p>&nbsp;</p>


</body>
</html>
<?php
mysql_free_result($Recordset1);
?>

[smantscheff]

At least two blatant mistakes in that code.
1) You confuse the POST variables 'Conveyor Number' and 'id'. You do not have a field with name='id' in your form. Therefore you cannot use the value of $_POST.
2) INSERT into conveyor_number (Date) values(NOW(), CURTIME ()
will generate an error - your field list has only one field, but values list two.
And
3) it is semantically incorrect to first INSERT a record from your post variables and then INSERT a second record to keep track of the time of the first record. The 2nd INSERT statement must either be an update statement, or, still better, set the time record at the same time when you insert values into the table. Also the statement

mysql_query("INSERT into conveyor_number (Date) values(NOW(), CURTIME ()")

is syntactically wrong (missing closing bracket).

In general, I recommend that you first learn mysql using the mysql command line client, and then start coding PHP programs which make use of MySQL.