bladechob Posted June 14, 2007 Share Posted June 14, 2007 I'm banging my head and various other body parts trying to get round this. What I'm trying to to is get one 'submit' to add values to two seperate tables in the same database. The object is to commit info to one table that shows a users preference for something and then for it to display under a separte wider search. In this case you search a database (venue_main) for a hotel in s specific goegraphical area and back comes a list with pertinent details. I've added a field to the search that now includes (or should do) whether venues that show on the search return are of a 'preferred' status to specific clients. This comes out of a table called client_pref_venue. The php file is writing to client_pref_venue but not venue_main. The only way I can get it to diplay on a web page is by manually entering into the database. Below is the bit where I think I'm messing up - any help muchos gracias: []<? require_once("cfg.php"); require_once($CFG->include_dir . "/config.php"); require_once($CFG->include_dir . "/weblib.php"); require_once($CFG->include_dir . "/dblib.php"); error_reporting(1); dbconnect(); // add new relationship $hcg=$_POST['hotelclientgroup']; if(!empty($hcg)){ $sql2="INSERT INTO client_pref_venue VALUES ('','$hotelid','$hcg','$pegsCode')"; if(!$insert=mysql_query($sql2,$conn))echo mysql_error(); } dbconnect(); // add to venuemain db $hcg=$_POST['hotelclientgroup']; if(!empty($hcg)){ $sql4="INSERT INTO venue_main VALUES ('','$hotelclientgroup','$hotelid','$name','$groupname','$address1','$address2','$address3','$town','$county','$tel_main','$postcode','$country','$tel_res','$tel_conf','$fax_main','$fax_res','$fax_conf','$email_main','$email_res','$rating','$type','$region','$special','$rooms','$parkins','$airport','$airportdist','$motorway','$motorwaydist','$railway','$railwaydist','$notes','$acctel','$accfax','$accemail','$currency','$cancpol','$terms','$extpark','$parkdist','$free','$website','$graphic','$pagabflag','$kcflag','$cceflag','$kcpagabflag','$description','$blacklisted')"; if(!$insert=mysql_query($sql4,$conn))echo mysql_error(); } //if delete chosen $delete=$_GET['delete']; if($delete!=''){ $sql3="DELETE from client_pref_venue where client_pref_id='$delete'"; if(!$delete=mysql_query($sql3,$conn))echo mysql_error(); } // for later $sql="SELECT * FROM client_pref_venue where hotelid='$hotelid'"; $prefs=mysql_query($sql,$conn); ?>[] Quote Link to comment https://forums.phpfreaks.com/topic/55573-fresh-pair-of-eyes-needed-please-phpsql-prob/ Share on other sites More sharing options...
simcoweb Posted June 14, 2007 Share Posted June 14, 2007 Is this throwing an error? Quote Link to comment https://forums.phpfreaks.com/topic/55573-fresh-pair-of-eyes-needed-please-phpsql-prob/#findComment-274689 Share on other sites More sharing options...
Nhoj Posted June 14, 2007 Share Posted June 14, 2007 My guess would be the 2nd dbconnect(); clause... You shouldn't need a 2nd one in there... Quote Link to comment https://forums.phpfreaks.com/topic/55573-fresh-pair-of-eyes-needed-please-phpsql-prob/#findComment-274694 Share on other sites More sharing options...
bladechob Posted June 14, 2007 Author Share Posted June 14, 2007 Nope it's not throwing an error and I've tried it without the second db clause and still no joy Quote Link to comment https://forums.phpfreaks.com/topic/55573-fresh-pair-of-eyes-needed-please-phpsql-prob/#findComment-274699 Share on other sites More sharing options...
simcoweb Posted June 14, 2007 Share Posted June 14, 2007 There's some repetitious elements in these: dbconnect(); // add new relationship $hcg=$_POST['hotelclientgroup']; if(!empty($hcg)){ $sql2="INSERT INTO client_pref_venue VALUES ('','$hotelid','$hcg','$pegsCode')"; if(!$insert=mysql_query($sql2,$conn))echo mysql_error(); } dbconnect(); // add to venuemain db $hcg=$_POST['hotelclientgroup']; if(!empty($hcg)){ $sql4="INSERT INTO venue_main VALUES ('','$hotelclientgroup','$hotelid','$name','$groupname','$address1','$address2','$address3','$town','$county','$tel_main','$postcode','$country','$tel_res','$tel_conf','$fax_main','$fax_res','$fax_conf','$email_main','$email_res','$rating','$type','$region','$special','$rooms','$parkins','$airport','$airportdist','$motorway','$motorwaydist','$railway','$railwaydist','$notes','$acctel','$accfax','$accemail','$currency','$cancpol','$terms','$extpark','$parkdist','$free','$website','$graphic','$pagabflag','$kcflag','$cceflag','$kcpagabflag','$description','$blacklisted')"; if(!$insert=mysql_query($sql4,$conn))echo mysql_error(); } Try this. See my notes included: dbconnect(); // add new relationship $hcg=$_POST['hotelclientgroup']; if(!empty($hcg)){ $sql2="INSERT INTO client_pref_venue VALUES ('','$hotelid','$hcg','$pegsCode')"; if(!$insert=mysql_query($sql2,$conn))echo mysql_error(); } // dbconnect(); //only need this once // add to venuemain db // $hcg=$_POST['hotelclientgroup']; // don't need this as it's already declared if(!empty($hcg)){ $sql4="INSERT INTO venue_main VALUES ('','$hotelclientgroup','$hotelid','$name','$groupname','$address1','$address2','$address3','$town','$county','$tel_main','$postcode','$country','$tel_res','$tel_conf','$fax_main','$fax_res','$fax_conf','$email_main','$email_res','$rating','$type','$region','$special','$rooms','$parkins','$airport','$airportdist','$motorway','$motorwaydist','$railway','$railwaydist','$notes','$acctel','$accfax','$accemail','$currency','$cancpol','$terms','$extpark','$parkdist','$free','$website','$graphic','$pagabflag','$kcflag','$cceflag','$kcpagabflag','$description','$blacklisted')"; // if(!$insert=mysql_query($sql4,$conn))echo mysql_error(); // let's change this to below $results = mysql_query($sql4) or die(mysql_error()); if(!results) { echo "something is wrong with the data insertion."; } } Quote Link to comment https://forums.phpfreaks.com/topic/55573-fresh-pair-of-eyes-needed-please-phpsql-prob/#findComment-274708 Share on other sites More sharing options...
bladechob Posted June 14, 2007 Author Share Posted June 14, 2007 Thanks - I've just inserted it into the file and still no joy or errors! Quote Link to comment https://forums.phpfreaks.com/topic/55573-fresh-pair-of-eyes-needed-please-phpsql-prob/#findComment-274715 Share on other sites More sharing options...
simcoweb Posted June 14, 2007 Share Posted June 14, 2007 Ok, quick question. You have just one variable, $hcg=$_POST['hotelclientgroup']; but about 30 fields. You need to set the values for each of those. Where is that being done? If there's no errors being thrown then something is being inserted. Most likely just the auto-incremented id field and the 'hotelclientgroup' field since that's the only variable being given a value in the $_POST. Quote Link to comment https://forums.phpfreaks.com/topic/55573-fresh-pair-of-eyes-needed-please-phpsql-prob/#findComment-274718 Share on other sites More sharing options...
bladechob Posted June 14, 2007 Author Share Posted June 14, 2007 Not quite sure on that as I 'inherited this file from a previous coder - here's the file in it's ugly full self if that helps: <? require_once("cfg.php"); require_once($CFG->include_dir . "/config.php"); require_once($CFG->include_dir . "/weblib.php"); require_once($CFG->include_dir . "/dblib.php"); error_reporting(1); dbconnect(); // add new relationship $hcg=$_POST['hotelclientgroup']; if(!empty($hcg)){ $sql2="INSERT INTO client_pref_venue VALUES ('','$hotelid','$hcg','$pegsCode')"; if(!$insert=mysql_query($sql2,$conn))echo mysql_error(); } // dbconnect(); //only need this once // add to venuemain db // $hcg=$_POST['hotelclientgroup']; // don't need this as it's already declared if(!empty($hcg)){ $sql4="INSERT INTO venue_main VALUES ('','$hotelclientgroup','$hotelid','$name','$groupname','$address1','$address2','$address3','$town','$county','$tel_main','$postcode','$country','$tel_res','$tel_conf','$fax_main','$fax_res','$fax_conf','$email_main','$email_res','$rating','$type','$region','$special','$rooms','$parkins','$airport','$airportdist','$motorway','$motorwaydist','$railway','$railwaydist','$notes','$acctel','$accfax','$accemail','$currency','$cancpol','$terms','$extpark','$parkdist','$free','$website','$graphic','$pagabflag','$kcflag','$cceflag','$kcpagabflag','$description','$blacklisted')"; // if(!$insert=mysql_query($sql4,$conn))echo mysql_error(); // let's change this to below $results = mysql_query($sql4) or die(mysql_error()); if(!results) { echo "something is wrong with the data insertion."; } } // for later $sql="SELECT * FROM client_pref_venue where hotelid='$hotelid'"; $prefs=mysql_query($sql,$conn); ?> <html> <head> <title>Untitled Document</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <script language="JavaScript" src="windowopen.js"></script> <link rel="StyleSheet" type="text/css" href="style.css"> </head> <body topmargin="0"> <table width="100%" border="0" cellspacing="1" cellpadding="4" class="hotel"> <tr> <td width="11%"> </td> <td width="57%" valign="bottom"><font size="1">ADD CLIENT GROUP PREFERENCE</font></td> <td width="32%" align="right"><? //addContextHelp('client_prefs');?></td> </tr> <form action=<? echo $_SERVER['PHP_SELF'] . "?hotelid=$hotelid"; ?> method="post"> <tr> <td> </td> <td colspan="2"> <select name="hotelclientgroup" length=20> <? $queryu = "select ClientGroup from clients group by ClientGroup "; $resultu=mysql_query($queryu,$conn); while ($r2= mysql_fetch_array($resultu)) { $this=$r2[0];?> <option><? echo $r2[0];?></option> <? }?> </select> <input type="submit" name="Submit" value="Add"> </td> </tr> <tr> <td> </td> <td> </td> <td> </td> </tr></form> <tr> <td> </td> <td><font size="1">EXISTING CLIENT GROUP PREFERENCE(S) FOR THIS VENUE:</font></td> <td> </td> </tr> <? if(mysql_num_rows($prefs)==0){echo "<tr><td></td><td><b>No results found.</b></td><td></td></tr>";}else{ while($pref=mysql_fetch_array($prefs)){ $id=$pref['client_pref_id']; $cg=$pref['clientGroup']; echo "<tr> <td> </td> <td>$cg</td> <td><a href=" . $_SERVER['PHP_SELF'] . "?hotelid=$hotelid&delete=$id><b>delete</b></td> </tr>"; }}?> </table> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/55573-fresh-pair-of-eyes-needed-please-phpsql-prob/#findComment-274726 Share on other sites More sharing options...
Nhoj Posted June 14, 2007 Share Posted June 14, 2007 Your coder should honestly be shot, but none the less, posting a snippet of your "create table" statement from an SQL export for the table that doesn't seem to be updating might help a little. - John Quote Link to comment https://forums.phpfreaks.com/topic/55573-fresh-pair-of-eyes-needed-please-phpsql-prob/#findComment-274728 Share on other sites More sharing options...
bladechob Posted June 14, 2007 Author Share Posted June 14, 2007 Oh he went to Poland in the end - anyway the smaller file looks so: -- Table structure for table `client_pref_venue` -- CREATE TABLE `client_pref_venue` ( `client_pref_id` int(11) NOT NULL auto_increment, `hotelid` int(11) NOT NULL default '0', `clientGroup` varchar(40) NOT NULL default '', `pegsCode` varchar(20) NOT NULL default '', PRIMARY KEY (`client_pref_id`), KEY `clientGroup` (`clientGroup`) ) TYPE=MyISAM AUTO_INCREMENT=110 ; -- -- Dumping data for table `client_pref_venue` The larger like this: CREATE TABLE `venue_main` ( `hotelid` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `groupname` varchar(30) NOT NULL default '', `address1` varchar(30) NOT NULL default '', `address2` varchar(30) NOT NULL default '', `address3` varchar(30) NOT NULL default '', `town` varchar(20) NOT NULL default '', `county` varchar(20) NOT NULL default '', `postcode` varchar(10) NOT NULL default '', `country` varchar(30) NOT NULL default '', `tel_main` varchar(30) NOT NULL default '', `tel_res` varchar(30) NOT NULL default '', `tel_conf` varchar(30) NOT NULL default '', `fax_main` varchar(30) NOT NULL default '', `fax_res` varchar(30) NOT NULL default '', `fax_conf` varchar(30) NOT NULL default '', `email_main` varchar(60) NOT NULL default '', `email_res` varchar(60) NOT NULL default '', `email_conf` varchar(60) NOT NULL default '', `rating` varchar(100) NOT NULL default '', `type` varchar(20) NOT NULL default '', `region` varchar(20) NOT NULL default '', `special` varchar(100) NOT NULL default '', `rooms` int(11) NOT NULL default '0', `parking` int(11) NOT NULL default '0', `airport` varchar(30) NOT NULL default '', `airportdist` float(8,1) NOT NULL default '0.0', `motorway` varchar(30) NOT NULL default '', `motorwaydist` float(8,1) NOT NULL default '0.0', `railway` varchar(30) NOT NULL default '', `railwaydist` float(8,1) NOT NULL default '0.0', `notes` text NOT NULL, `currency` varchar(50) NOT NULL default '', `acctel` varchar(20) NOT NULL default '', `accfax` varchar(20) NOT NULL default '', `accemail` varchar(50) NOT NULL default '', `cancpol` varchar(150) NOT NULL default '', `terms` varchar(150) NOT NULL default '', `extpark` varchar(100) NOT NULL default '', `parkdist` varchar(100) NOT NULL default '', `free` tinyint(4) NOT NULL default '0', `website` varchar(70) NOT NULL default '', `graphic` varchar(50) NOT NULL default '', `pegabflag` tinyint(4) NOT NULL default '0', `kcflag` enum('0','1','2') NOT NULL default '0', `cceflag` enum('0','1','2') NOT NULL default '0', `kcpegabflag` tinyint(4) NOT NULL default '0', `hotelclientgroup` varchar(255) NOT NULL default '', `description` tinytext NOT NULL, `blacklisted` enum('N','Y') NOT NULL default 'N', PRIMARY KEY (`hotelid`) ) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=11234 ; Quote Link to comment https://forums.phpfreaks.com/topic/55573-fresh-pair-of-eyes-needed-please-phpsql-prob/#findComment-274736 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.