Unexpected MYSQL record value when using PHP to insert a table record

Page 1 of 1

9 Replies - 931 Views - Last Post: 30 June 2009 - 12:42 AM Rate Topic: -----

#1 A_Sanders@tpg.com.au  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 26-June 09

Unexpected MYSQL record value when using PHP to insert a table record

Posted 27 June 2009 - 08:39 PM

Hi,

Am having an issue inserting a user selected drop down list value into a MYSQL DB table via a form.

The DDL values which display correctly, are 1 through to 20 and displayed dynamically via the mysql_fetch_assoc function using a basic select * from table query. The MYSQL table field (bet_type_id) type is SMALLINT(3). Whenever I select the bet_type_id from the drop down list and submit the form, the resultant MYSQL table field is updated with zero regardless of what value I have selected.

Any help would be greatly appreciated. I think the issue appears to be with the way I am using the GetSQLValueString($_POST['bet_type_id'], "int") part of the code. When I remove the code and hard-code the value to say 1, the value is successfully inserted into the betslip table bet_type_id field as '1'.

Thanks

<?php

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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"] == "log_betslip_test")) {
  $insertSQL = 	sprintf("INSERT INTO betslip (bet_date,punter_id,category_id,bet_agency_id,bet_amt,bet_odds,bet_round_id,bet_type_id) 
				  VALUES (%s,%d,%d,%d,%s,%s,%d,%d)", 
				  'now()',
				1,
				GetSQLValueString($_POST['category_id'], "defined"),
				1,
				GetSQLValueString($_POST['bet_amount'], "double"),
				GetSQLValueString($_POST['odds'], "double"),
				2,
				GetSQLValueString($_POST['bet_type_id'], "int"));


Is This A Good Question/Topic? 0
  • +

Replies To: Unexpected MYSQL record value when using PHP to insert a table record

#2 no2pencil  Icon User is online

  • Original Digital Gansta
  • member icon

Reputation: 4503
  • View blog
  • Posts: 24,971
  • Joined: 10-May 07

Re: Unexpected MYSQL record value when using PHP to insert a table record

Posted 28 June 2009 - 01:03 AM

** Moved to PHP **
Was This Post Helpful? 0
  • +
  • -

#3 ghqwerty  Icon User is offline

  • if($spareTime > 0){ $this->writeCode(); }
  • member icon

Reputation: 40
  • View blog
  • Posts: 876
  • Joined: 08-August 08

Re: Unexpected MYSQL record value when using PHP to insert a table record

Posted 28 June 2009 - 01:18 AM

try echoing out that post data then across your code and then find out where it is changed, could you also post the form please? there might be an error in that
Was This Post Helpful? 0
  • +
  • -

#4 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,289
  • Joined: 12-March 09

Re: Unexpected MYSQL record value when using PHP to insert a table record

Posted 28 June 2009 - 02:17 AM

try this code
<?php

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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"] == "log_betslip_test")) {
  $insertSQL =     sprintf("INSERT INTO betslip (bet_date,punter_id,category_id,bet_agency_id,bet_amt,bet_odds,bet_round_id,b
et_type_id) 
                  VALUES (%s,%d,%d,%d,%s,%s,%d,%d)", 
                  'now()',
                1,
                GetSQLValueString($_POST['category_id'], "defined"),
                1,
                GetSQLValueString($_POST['bet_amount'], "double"),
                GetSQLValueString($_POST['odds'], "double"),
                2,
                GetSQLValueString($_POST['bet_type_id'], "int"));

Was This Post Helpful? 0
  • +
  • -

#5 A_Sanders@tpg.com.au  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 26-June 09

Re: Unexpected MYSQL record value when using PHP to insert a table record

Posted 29 June 2009 - 01:00 AM

Thanks ghqwerty. Any help much appreciated. Form code below.
<form action="<?php echo $editFormAction; ?>" method="POST" enctype="multipart/form-data" name="log_betslip_test">
<table width="600" border="1" class="style">

  <table width="800">
	<tr>
	  <th width="200" scope="col">Bet Amount </th>
	  <th scope="col"><div align="left">
		<input type="text" name="textfield">
	  </div></th>
	</tr>
	<tr>
	  <td><div align="center">Category</div></td>
	  <td>
		<div align="left">
			<select name="category_list" size="1" id="category_list">
			  <?php
do {  
?>
			  <option value="<?php echo $row_bet_categories_query['category_id']?>"><?php echo $row_bet_categories_query['category_id']?></option>
			  <?php
} while ($row_bet_categories_query = mysql_fetch_assoc($bet_categories_query));
  $rows = mysql_num_rows($bet_categories_query);
  if($rows > 0) {
	  mysql_data_seek($bet_categories_query, 0);
	  $row_bet_categories_query = mysql_fetch_assoc($bet_categories_query);
  }
?>
			</select>
		</div></td></tr>
	<tr>
	  <td><div align="center">Sport</div></td>
	  <td>
		<div align="left">
			<select name="sports_list" size="1" id="sports_list">
			  <?php
do {  
?>
			  <option value="<?php echo $row_sport_query['bet_type_id']?>"<?php if (!(strcmp($row_sport_query['bet_type_id'], $row_sport_query['bet_type_id']))) {echo "SELECTED";} ?>><?php echo $row_sport_query['bet_type_id']?></option>
			  <?php
} while ($row_sport_query = mysql_fetch_assoc($sport_query));
  $rows = mysql_num_rows($sport_query);
  if($rows > 0) {
	  mysql_data_seek($sport_query, 0);
	  $row_sport_query = mysql_fetch_assoc($sport_query);
  }
?>
			</select>
		</div></td></tr>
	<tr>
	  <td><div align="center">Bet</div></td>
	  <td>
		<div align="left">
		  <input name="bet_amount" type="text" id="bet_amount" value="0.00" size="7" maxlength="5">
		</div></td></tr>
	<tr>
	  <td><div align="center">Odds</div></td>
	  <td>
		<div align="left">
		  <input name="odds" type="text" id="odds" value="0.00" size="7" maxlength="7">
		</div></td></tr>
	<tr>
	  <td><div align="center">Bookie</div></td>
	  <td>
		<div align="left">
			<select name="bet_agency_list" size="1" id="bet_agency_list">
			  <?php
do {  
?>
			  <option value="<?php echo $row_bookie_query['bet_agency_name']?>"<?php if (!(strcmp($row_bookie_query['bet_agency_name'], $row_bookie_query['bet_agency_name']))) {echo "SELECTED";} ?>><?php echo $row_bookie_query['bet_agency_name']?></option>
			  <?php
} while ($row_bookie_query = mysql_fetch_assoc($bookie_query));
  $rows = mysql_num_rows($bookie_query);
  if($rows > 0) {
	  mysql_data_seek($bookie_query, 0);
	  $row_bookie_query = mysql_fetch_assoc($bookie_query);
  }
  
?>
			</select>
		</div></td></tr>
	<tr>
	  <td>&nbsp;</td>
	  <td><div align="left"></div></td>
	</tr>
	<tr>
	  <td><input type="submit" name="Submit" value="Log Bet"></td>
	  <td><div align="left"></div></td>
	</tr>
	<tr>
	  <td><input type="reset" name="Submit2" value="Reset"></td>
	  <td><div align="left"></div></td>
	</tr>
  </table></br>
  
  <p>&nbsp;</p>
  <input type="hidden" name="MM_insert" value="log_betslip_test">
</form>

Was This Post Helpful? 0
  • +
  • -

#6 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,289
  • Joined: 12-March 09

Re: Unexpected MYSQL record value when using PHP to insert a table record

Posted 29 June 2009 - 01:22 AM

in Your HTML Form You Your Select id and name is sports_list and you are calling as bet_type_id.
Here is the code
<?php
?php

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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"] == "log_betslip_test")) {
  $insertSQL =     sprintf("INSERT INTO betslip & #40;bet_date,punter_id,category_id,bet_agency_id,bet_amt,bet_odds,bet_round_id,b
et_type_id)
                  VALUES (%s,%d,%d,%d,%s,%s,%d,%d)",
                  'now()',
                1,
                GetSQLValueString($_POST['category_id'], "defined"),
                1,
                GetSQLValueString($_POST['bet_amount'], "double"),
                GetSQLValueString($_POST['odds'], "double"),
                2,
                GetSQLValueString($_POST['sports_list'], "int"));
?>
<form action="<?php echo $editFormAction; ?>" method="POST" enctype="multipart/form-data" name="log_betslip_test">
<table width="600" border="1" class="style">

  <table width="800">
    <tr>
      <th width="200" scope="col">Bet Amount </th>
      <th scope="col"><div align="left">
        <input type="text" name="textfield">
      </div></th>
    </tr>
    <tr>
      <td><div align="center">Category</div></td>
      <td>
        <div align="left">
            <select name="category_list" size="1" id="category_list">
              <?php
do {  
?>
              <option value="<?php echo $row_bet_categories_query['category_id']?>"><?php echo $row_bet_categories_query['category_id']?></option>
              <?php
} while ($row_bet_categories_query = mysql_fetch_assoc($bet_categories_query));
  $rows = mysql_num_rows($bet_categories_query);
  if($rows > 0) {
      mysql_data_seek($bet_categories_query, 0);
      $row_bet_categories_query = mysql_fetch_assoc($bet_categories_query);
  }
?>
            </select>
        </div></td></tr>
    <tr>
      <td><div align="center">Sport</div></td>
      <td>
        <div align="left">
            <select name="sports_list" size="1" id="sports_list">
              <?php
do {  
?>
              <option value="<?php echo $row_sport_query['bet_type_id']?>"<?php if (!(strcmp($row_sport_query['bet_type_id'], $row_sport_query['bet_type_id']))) {echo "SELECTED";} ?>><?php echo $row_sport_query['bet_type_id']?></option>
              <?php
} while ($row_sport_query = mysql_fetch_assoc($sport_query));
  $rows = mysql_num_rows($sport_query);
  if($rows > 0) {
      mysql_data_seek($sport_query, 0);
      $row_sport_query = mysql_fetch_assoc($sport_query);
  }
?>
            </select>
        </div></td></tr>
    <tr>
      <td><div align="center">Bet</div></td>
      <td>
        <div align="left">
          <input name="bet_amount" type="text" id="bet_amount" value="0.00" size="7" maxlength="5">
        </div></td></tr>
    <tr>
      <td><div align="center">Odds</div></td>
      <td>
        <div align="left">
          <input name="odds" type="text" id="odds" value="0.00" size="7" maxlength="7">
        </div></td></tr>
    <tr>
      <td><div align="center">Bookie</div></td>
      <td>
        <div align="left">
            <select name="bet_agency_list" size="1" id="bet_agency_list">
              <?php
do {  
?>
              <option value="<?php echo $row_bookie_query['bet_agency_name']?>"<?php if (!(strcmp($row_bookie_query['bet_agency_name'], $row_bookie_query['bet_agency_name']))) {echo "SELECTED";} ?>><?php echo $row_bookie_query['bet_agency_name']?></option>
              <?php
} while ($row_bookie_query = mysql_fetch_assoc($bookie_query));
  $rows = mysql_num_rows($bookie_query);
  if($rows > 0) {
      mysql_data_seek($bookie_query, 0);
      $row_bookie_query = mysql_fetch_assoc($bookie_query);
  }
 
?>
            </select>
        </div></td></tr>
    <tr>
      <td> </td>
      <td><div align="left"></div></td>
    </tr>
    <tr>
      <td><input type="submit" name="Submit" value="Log Bet"></td>
      <td><div align="left"></div></td>
    </tr>
    <tr>
      <td><input type="reset" name="Submit2" value="Reset"></td>
      <td><div align="left"></div></td>
    </tr>
  </table></br>
 
  <p> </p>
  <input type="hidden" name="MM_insert" value="log_betslip_test">
</form>

Hope it Help You :)
Was This Post Helpful? 0
  • +
  • -

#7 A_Sanders@tpg.com.au  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 26-June 09

Re: Unexpected MYSQL record value when using PHP to insert a table record

Posted 29 June 2009 - 04:55 AM

Thanks mate. That did the trick! Much appreciated.
Was This Post Helpful? 0
  • +
  • -

#8 A_Sanders@tpg.com.au  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 26-June 09

Re: Unexpected MYSQL record value when using PHP to insert a table record

Posted 29 June 2009 - 05:02 AM

Noor,

One more thing. In that example I'm inserting a selected code value into the mysql table. If I chose to show a description value in the drop down list I present to the end-user, instead of a code value but I wanted to insert a code value based on that selection, how would I achieve this?

I.e. instead of bet_type_id, I show the respective bet_description value to the user, but once they select the bet_description and submit the form, I insert the bet_type_id value corresponding to that bet_description value into the database table.

Using the below example I present aaaaa, bbbbb and ccccc in the drop down list but when I run the insert statement, it inserts 1, 2 or 3 based on the description the user selected.

bet_type_id bet_description
1 aaaaa
2 bbbbb
3 cccccc

Again, any help much appreciated.
Was This Post Helpful? 0
  • +
  • -

#9 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,289
  • Joined: 12-March 09

Re: Unexpected MYSQL record value when using PHP to insert a table record

Posted 29 June 2009 - 09:30 PM

Then You Must Type Description in Your Select Value such as
<select name="desc" id="desc">
    <option value="des1">des1</option>
    <option value="des2">des2</option>
    <option value="des3">des3</option>
    <option value="des4">des4</option>
</select>

Was This Post Helpful? 0
  • +
  • -

#10 A_Sanders@tpg.com.au  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 26-June 09

Re: Unexpected MYSQL record value when using PHP to insert a table record

Posted 30 June 2009 - 12:42 AM

Thanks Noor. Doesn't that hard-code the drop down list values? What I wanted to do was dynamically show the available bet_description values in the drop down list based on a bet type table and insert the corresponding bet_type_id value into the betslip table. Have attached the form code pertaining to the drop down list in question below. How would you code it so I am able to show the description but insert the corresonding ID in the sprintf statement provided previously.

	<tr>
	  <td><div align="center">Sport</div></td>
	  <td>
		<div align="left">
			<select name="sports_list" size="1" id="sports_list">
			  <?php
do {  
?>
			  <option value="<?php echo $row_sport_query['bet_type_id']?>"<?php if (!(strcmp($row_sport_query['bet_type_id'], $row_sport_query['bet_type_id']))) {echo "SELECTED";} ?>><?php echo $row_sport_query['bet_type_id']?></option>
			  <?php
} while ($row_sport_query = mysql_fetch_assoc($sport_query));
  $rows = mysql_num_rows($sport_query);
  if($rows > 0) {
	  mysql_data_seek($sport_query, 0);
	  $row_sport_query = mysql_fetch_assoc($sport_query);
  }
?>
			</select>

Was This Post Helpful? 0
  • +
  • -
Page 1 of 1