Fresh pair of eyes needed please! PHP/SQL prob

bladechob · 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);

?>[]

simcoweb · June 14, 2007

Is this throwing an error?

Nhoj · June 14, 2007

My guess would be the 2nd dbconnect(); clause... You shouldn't need a 2nd one in there...

bladechob · June 14, 2007

Nope it's not throwing an error and I've tried it without the second db clause and still no joy

simcoweb · 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.";
}
}

bladechob · June 14, 2007

Thanks - I've just inserted it into the file and still no joy or errors!

simcoweb · 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.

bladechob · 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>

</head>

<tr>

<td width="57%" valign="bottom"><font size="1">ADD CLIENT GROUP PREFERENCE</font></td>

</tr>

<form action=<? echo $_SERVER['PHP_SELF'] . "?hotelid=$hotelid"; ?> method="post">

<tr>

<?

$queryu = "select ClientGroup from clients group by ClientGroup ";

$resultu=mysql_query($queryu,$conn);

while ($r2= mysql_fetch_array($resultu)) {

$this=$r2[0];?>

<? }?>

</select>

</tr>

<tr>

</tr></form>

<tr>

<td><font size="1">EXISTING CLIENT GROUP PREFERENCE(S) FOR THIS VENUE:</font></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><a href=" . $_SERVER['PHP_SELF'] . "?hotelid=$hotelid&delete=$id><b>delete</b></td>

</tr>";

}}?>

</table>

</body>

</html>

Nhoj · 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

bladechob · 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 ;

Sign In

Fresh pair of eyes needed please! PHP/SQL prob

Recommended Posts

bladechob

Link to comment

Share on other sites

simcoweb

Link to comment

Share on other sites

Nhoj

Link to comment

Share on other sites

bladechob

Link to comment

Share on other sites

simcoweb

Link to comment

Share on other sites

bladechob

Link to comment

Share on other sites

simcoweb

Link to comment

Share on other sites

bladechob

Link to comment

Share on other sites

Nhoj

Link to comment

Share on other sites

bladechob

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information