Help writing to 2 tables

[movieflick]

Ok I am new to php and need some help.

I have created a form that rights to a customers table I have a second table that contains shop information. What I I am trying to do is have the customer fill out the form and based on the zip code he/she enters right the name of the shop contained in the shop table with a matching zipcode to a colums in the customers table. I am not sure how to query and write in two different tables at the same time. I hope I explained that well enough. I would greatly appreciate any help.

 

[jpadie]

you cannot write queries to two different tables at the same time. in some rdbms it is possible to create a view across multiple tables that is updatable.

the normal way around this functional restriction is to construct two queries and issue them separately against the tables. if your db is set up properly you can bundle these queries into a single transaction so that if either of them fail they can both be rolled back. i believe that the innodb format supports this within mysql. read the mysql manual on BEGIN and COMMIT transactions.

 

[vacunita]

You dont' need or would ever need to[red]write[/red] and read to the DB at the smae time.

The process here would be:

1. Process form,
1.a Get Zip code from form
2. Execute query to find shop with matching zipcode.
3. get shop info from query,
4. Plug shop info into query for user data.
5. execute query
6. if successfull show thankyou page opr something.

In other words, do oner thing at a time in your script, checking that everytrhing is as expected.

In pseudocode it would be:

IF(form is submitted)
zipcodequery=SELECT *from shops where zipcode=zipcode from user;

execute query.

check results. 

then get shop info from resultset such as:

$shop=$results['shopname'];

construct insert query

query=INSERT INTO users values(username,email,...$shop,$whatever else from the results of the previous query.)

execute query.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[skiflyer]

I disagree, you could certainly need to read/write to the db at the same time.

subqueries/stored functions/transactions etc. can all assist with this, but we need more information regarding your database in order to answer the question.

 

[movieflick]

Ok the two tables are frm_cust and frm_shop there are several columns in each but the ones that are of inportance are frm_shop.sname, frm_shop.sassignzip the are the tables to be read and frm_cust.czip is where the dynamic data will be gathered and frm_shop.sname will be writen to frm_cust.refto where frm_shop.sassignzip = frm_cust.czip. The query I have been using is :

mysql_select_db($database_dent, $dent);
$query_sname = "SELECT frm_shop.sname FROM frm_shop WHERE frm_shop.sassignzip = 80918 ";
$sname = mysql_query($query_sname, $dent) or die(mysql_error());
$row_sname = mysql_fetch_assoc($sname);
$totalRows_sname = mysql_num_rows($sname);

and it works perfect but I need it to be a dynamic value so when I change
frm_shop.sassignzip = 80918 ";

to

frm_shop.sassignzip = czip";
I get nothing. I think I have 2 problems I am not correctly declairing czip as a variable and/or I am not properly running the query. Here is the entire form I used dreamweaver extensions called MXKollection to help me make to for so all the non standard stuff is caused by that.

<?php require_once('Connections/dent.php'); ?>
<?php
// Load the common classes
require_once('includes/common/KT_common.php');

// Load the tNG classes
require_once('includes/tng/tNG.inc.php');

// Make a transaction dispatcher instance
$tNGs = new tNG_dispatcher("");

// Make unified connection variable
$conn_dent = new KT_connection($dent, $database_dent);

// Start trigger
$formValidation = new tNG_FormValidation();
$formValidation->addField("cfname", true, "text", "", "", "", "Please enter your First Name.");
$formValidation->addField("clname", true, "text", "", "", "", "Please enter your Last Name.");
$formValidation->addField("czip", true, "text", "zip_us5", "", "", "Please enter a valid Zip Code.");
$formValidation->addField("cemail", true, "text", "email", "", "", "Please enter a valid Email Address.");
$formValidation->addField("cphone", true, "text", "phone", "", "", "Please enter a valid Phone Number.");
$tNGs->prepareValidation($formValidation);
// End trigger

mysql_select_db($database_dent, $dent);
$query_sname = "SELECT frm_shop.sname FROM frm_shop WHERE frm_shop.sassignzip = 'czip' ";
$sname = mysql_query($query_sname, $dent) or die(mysql_error());
$row_sname = mysql_fetch_assoc($sname);
$totalRows_sname = mysql_num_rows($sname);

// Make an insert transaction instance
$ins_frm_cust = new tNG_insert($conn_dent);
$tNGs->addTransaction($ins_frm_cust);
// Register triggers
$ins_frm_cust->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "KT_Insert1");
$ins_frm_cust->registerTrigger("BEFORE", "Trigger_Default_FormValidation", 10, $formValidation);
$ins_frm_cust->registerTrigger("END", "Trigger_Default_Redirect", 99, "index.php");
// Add columns
$ins_frm_cust->setTable("frm_cust");
$ins_frm_cust->addColumn("cfname", "STRING_TYPE", "POST", "cfname");
$ins_frm_cust->addColumn("clname", "STRING_TYPE", "POST", "clname");
$ins_frm_cust->addColumn("crefto", "STRING_TYPE", "POST", "crefto", "{sname.sname}");
$ins_frm_cust->addColumn("cip", "STRING_TYPE", "POST", "cip", "{SERVER.REMOTE_ADDR}");
$ins_frm_cust->addColumn("czip", "STRING_TYPE", "POST", "czip");
$ins_frm_cust->addColumn("cemail", "STRING_TYPE", "POST", "cemail");
$ins_frm_cust->addColumn("cphone", "STRING_TYPE", "POST", "cphone");
$ins_frm_cust->addColumn("cyr", "STRING_TYPE", "POST", "cyr");
$ins_frm_cust->addColumn("cmk", "STRING_TYPE", "POST", "cmk");
$ins_frm_cust->addColumn("cmodel", "STRING_TYPE", "POST", "cmodel");
$ins_frm_cust->addColumn("crefrm", "STRING_TYPE", "POST", "crefrm");
$ins_frm_cust->addColumn("ccmnts", "STRING_TYPE", "POST", "ccmnts");
$ins_frm_cust->setPrimaryKey("refid", "NUMERIC_TYPE");

// Execute all the registered transactions
$tNGs->executeTransactions();

// Get the transaction recordset
$rsfrm_cust = $tNGs->getRecordset("frm_cust");
$row_rsfrm_cust = mysql_fetch_assoc($rsfrm_cust);
$totalRows_rsfrm_cust = mysql_num_rows($rsfrm_cust);
?><!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=iso-8859-1" />
<title>Untitled Document</title>
<link href="includes/skins/mxkollection3.css" rel="stylesheet" type="text/css" media="all" />
<script src="includes/common/js/base.js" type="text/javascript"></script>
<script src="includes/common/js/utility.js" type="text/javascript"></script>
<script src="includes/skins/style.js" type="text/javascript"></script>
<?php echo $tNGs->displayValidationRules();?>
</head>

<body>

<?php
echo $tNGs->getErrorMsg();
?>
<form method="post" id="form1" action="<?php echo KT_escapeAttribute(KT_getFullUri()); ?>">
<table cellpadding="2" cellspacing="0" class="KT_tngtable">
<tr>
<td class="KT_th"><label for="cfname">First Name:</label></td>
<td><input type="text" name="cfname" id="cfname" value="<?php echo KT_escapeAttribute($row_rsfrm_cust['cfname']); ?>" size="32" />
<?php echo $tNGs->displayFieldHint("cfname");?> <?php echo $tNGs->displayFieldError("frm_cust", "cfname"); ?> </td>
</tr>
<tr>
<td class="KT_th"><label for="clname">Last Name:</label></td>
<td><input type="text" name="clname" id="clname" value="<?php echo KT_escapeAttribute($row_rsfrm_cust['clname']); ?>" size="32" />
<?php echo $tNGs->displayFieldHint("clname");?> <?php echo $tNGs->displayFieldError("frm_cust", "clname"); ?> </td>
</tr>
<tr>
<td class="KT_th"><label for="czip">Zip Code:</label></td>
<td><input type="text" name="czip" id="czip" value="czip" size="32" />
<?php echo $tNGs->displayFieldHint("czip");?> <?php echo $tNGs->displayFieldError("frm_cust", "czip"); ?> </td>
</tr>
<tr>
<td class="KT_th"><label for="cemail">Email Address:</label></td>
<td><input type="text" name="cemail" id="cemail" value="<?php echo KT_escapeAttribute($row_rsfrm_cust['cemail']); ?>" size="32" />
<?php echo $tNGs->displayFieldHint("cemail");?> <?php echo $tNGs->displayFieldError("frm_cust", "cemail"); ?> </td>
</tr>
<tr>
<td class="KT_th"><label for="cphone">Phone Number:</label></td>
<td><input type="text" name="cphone" id="cphone" value="<?php echo KT_escapeAttribute($row_rsfrm_cust['cphone']); ?>" size="32" />
<?php echo $tNGs->displayFieldHint("cphone");?> <?php echo $tNGs->displayFieldError("frm_cust", "cphone"); ?> </td>
</tr>
<tr>
<td class="KT_th"><label for="cyr">Year:</label></td>
<td><input type="text" name="cyr" id="cyr" value="<?php echo KT_escapeAttribute($row_rsfrm_cust['cyr']); ?>" size="32" />
<?php echo $tNGs->displayFieldHint("cyr");?> <?php echo $tNGs->displayFieldError("frm_cust", "cyr"); ?> </td>
</tr>
<tr>
<td class="KT_th"><label for="cmk">Make:</label></td>
<td><input type="text" name="cmk" id="cmk" value="<?php echo KT_escapeAttribute($row_rsfrm_cust['cmk']); ?>" size="32" />
<?php echo $tNGs->displayFieldHint("cmk");?> <?php echo $tNGs->displayFieldError("frm_cust", "cmk"); ?> </td>
</tr>
<tr>
<td class="KT_th"><label for="cmodel">Model:</label></td>
<td><input type="text" name="cmodel" id="cmodel" value="<?php echo KT_escapeAttribute($row_rsfrm_cust['cmodel']); ?>" size="32" />
<?php echo $tNGs->displayFieldHint("cmodel");?> <?php echo $tNGs->displayFieldError("frm_cust", "cmodel"); ?> </td>
</tr>
<tr>
<td class="KT_th"><label for="crefrm">Where did you hear about us:</label></td>
<td><select name="crefrm" id="crefrm">
<option value="Google" <?php if (!(strcmp("Google", KT_escapeAttribute($row_rsfrm_cust['crefrm'])))) {echo "SELECTED";} ?>>Google</option>
<option value="Yahoo" <?php if (!(strcmp("Yahoo", KT_escapeAttribute($row_rsfrm_cust['crefrm'])))) {echo "SELECTED";} ?>>Yahoo</option>
<option value="Word of Mouth" <?php if (!(strcmp("Word of Mouth", KT_escapeAttribute($row_rsfrm_cust['crefrm'])))) {echo "SELECTED";} ?>>Word of Mouth</option>
<option value="Radio" <?php if (!(strcmp("Radio", KT_escapeAttribute($row_rsfrm_cust['crefrm'])))) {echo "SELECTED";} ?>>Radio</option>
<option value="TV" <?php if (!(strcmp("TV", KT_escapeAttribute($row_rsfrm_cust['crefrm'])))) {echo "SELECTED";} ?>>TV</option>
<option value="Billboard" <?php if (!(strcmp("Billboard", KT_escapeAttribute($row_rsfrm_cust['crefrm'])))) {echo "SELECTED";} ?>>Billboard</option>
<option value="Phone Book" <?php if (!(strcmp("Phone Book", KT_escapeAttribute($row_rsfrm_cust['crefrm'])))) {echo "SELECTED";} ?>>Phone Book</option>
<option value="Other" <?php if (!(strcmp("Other", KT_escapeAttribute($row_rsfrm_cust['crefrm'])))) {echo "SELECTED";} ?>>Other</option>
</select>
<?php echo $tNGs->displayFieldError("frm_cust", "crefrm"); ?> </td>
</tr>
<tr>
<td class="KT_th"><label for="ccmnts">Comments:</label></td>
<td><textarea name="ccmnts" id="ccmnts" cols="50" rows="5"><?php echo KT_escapeAttribute($row_rsfrm_cust['ccmnts']); ?></textarea>
<?php echo $tNGs->displayFieldHint("ccmnts");?> <?php echo $tNGs->displayFieldError("frm_cust", "ccmnts"); ?> </td>
</tr>
<tr class="KT_buttons">
<td colspan="2"><input type="submit" name="KT_Insert1" id="KT_Insert1" value="Insert record" />
</td>
</tr>
</table>
<input type="hidden" name="crefto" id="crefto" value="<?php echo KT_escapeAttribute($row_rsfrm_cust['crefto']); ?>" />
<input type="hidden" name="cip" id="cip" value="<?php echo KT_escapeAttribute($row_rsfrm_cust['cip']); ?>" />
</form>
<p>&nbsp;</p>
</body>
</html>
<?php
mysql_free_result($sname);
?>

 

[vacunita]

Ohh how I dislike all the overhead DW adds to a simple form validation.

Anyway
All variables in PHP must be preceded by a dollar sign [red]$[/red].

So your query should be:

$query_sname = "SELECT frm_shop.sname FROM frm_shop WHERE frm_shop.sassignzip =" . [red]$czip[/red];

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

should the criterion be in quotes or are all us zip codes purely numeric?

 

[vacunita]

As Fart As I know all U.S. Zip codes are purely numeric. No alpha characters.

Have never come across one that has letters or other non numeric characters . But you never know.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[movieflick]

yes all US zips are numeric

I tried to change $query_sname = "SELECT frm_shop.sname FROM frm_shop WHERE frm_shop.sassignzip = 'czip' ";

to

$query_sname = "SELECT frm_shop.sname FROM frm_shop WHERE frm_shop.sassignzip =" . $czip;

It throws an error

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

 

[skiflyer]

FYI: All US zips are numeric, but standard DB literature usually advocates storing them as characters as you will never do any math on them... why waste the overhead? a 10 digit numeric type will use more disk space than a 10 character string.

 

[jpadie]

movieflick:
try

$query_sname = "SELECT frm_shop.sname FROM frm_shop WHERE frm_shop.sassignzip ='$czip'";
[/oode]

 

[movieflick]

I tried that and got no errors but the information was not written to the database. I think the issue is a am querying and writing at the same time. I was hoping someone would be able to help. Time to hit the books I guess. Thanks anyway all.

 

[skiflyer]

You just need better debugging... MySQL is throwing you a very clear error... have you tried just plain printing the query you're sending before sending it?... from that error I'm guessing you're using a reserved word, have a stray quote, or are sending an empty query.

Just for kicks here's an old function I had laying around for use with PostgreSQL that makes debugging easier... should be trivial to modify for use with MySQL, feel free to use it if you like.

/**
 * pg_query() wrapper which includes settings for error handling
 *
 * @param str $query
 * @param str $function
 * @param resource $conn
 * @param bool $fatal_error
 * @param bool $show_error
 * @param bool $show_sql
 * @return mixed
 */
function pg_query_hg($query, $function, $conn=false, $fatal_error=true, $show_error=true, $show_sql=true)
{
	//show_sql should be set to false for deployment
	//false $conn means use default connection
	if ($conn !== false)
	{
		$results = pg_query($conn, $query);
	}
	else
	{
		$results = pg_query($query);
	}

	if ($results === false)
	{
		if ($show_sql)
		{
			echo $query.'<br /><br />'.$function;
		}
		if ($show_error === true)
		{
			$err.='<pre>';
			$err.= pg_result_error($results);
		}
		else
		{
			$err = '';
		}
		if ($fatal_error === true)
		{
			die($err);
		}
		else
		{
			return $results;
		}
	}
	return $results;
}

 

[movieflick]

Ok I will give it a try. I am new to all this so trying to learn as I go. Thanks for the info.

 

[Vragabond]

Have you verified that $czip has the information you want? Since it is coming from the posted form, I would expect it to be in the $_POST['czip'] array rather than just a declared variable.