Inserting to mySQL Strange problem????

[sanjdhiman]

Hi there a simple question im sure for most of you guys.

Ok im running php 4 with apache and mySQL in the normal way
on win xp home

ok i have this problem i do the following insert statements when someone new registers. that is fine these all work . Thye are tried and tested.

function register ($emailadd, $pword, $fname, $lname, $number, $street, $county, $citytown, $postcode, $telno, $workno, $faxno)
// register a new person to the database
// return true if successful and false if not

{
$connection = db_connect();

if (!$connection)
return " Could not connect to database server, please try again";

// make sure user is not already entered in the database

$result = mysql_query("SELECT * FROM userlog WHERE username='$emailadd'"
if (!($result))
return "Could not execute query";
if (mysql_num_rows($result)>0)
return "That email address is taken, go back and choose another one. ";

// if all is well add user to database
//first add in the personal details first so that we can get the next new userID hence use that ID
//to match the userdetails to the userlogin details and the userattributes

$startdate=date("Y/m/d"
$endtimestamp = strtotime("+90 days"
$enddate=date('Y/m/d', $endtimestamp);

$result = mysql_query("INSERT into regusers (FirstName,
LastName,
HouseNumber,
StreetName,
CityTown,
County,
Postcode,
DateRegistered,
EndOfRegistration,
HTelephone,
WTelephone,
Fax,
Email,
FlaggedDeleted,
AccountStatus) VALUES ('$fname', '$lname', '$number', '$street', '$citytown', '$county', '$postcode', '$startdate', '$enddate', '$telno', '$workno', '$faxno', '$emailadd', '0', 'Live')"

$getuserid = mysql_query("SELECT UserID FROM regusers WHERE Email= '$emailadd'"
$row = mysql_fetch_array($getuserid);

$userid = stripslashes($row["UserID"]);


$result = mysql_query("INSERT into userlog (UserID,
UserName,
Password) VALUES ('$userid', '$emailadd', '$pword')"

$result = mysql_query("INSERT into userattr (UserID,
BgColour,
FgColour,
BorderColour,
FontSize,
FontFamily) VALUES('$userid', '0','0','0','0','0')"


Ok so they all work fine. Now just recently I created a new table in mySQL called userprofiles and i want to add info to this table as well when people register.

Ok so i set it up etc.. BUT i cant add to it. i even created a seperate page and nothing, it wont insert to mySQL to that particular table,

Below is the make up of the table, however i am when inside mysql able to INSERT into the table but when tryin it externally it wont let me.. I have checked the code over and over again but no errors can be seen?

CREATE TABLE userprofiles (
UserID int(5) unsigned zerofill NOT NULL DEFAULT '00000' ,
PropType varchar(20) NOT NULL DEFAULT '' ,
PriceMin int(6) unsigned ,
PriceMax int(7) unsigned ,
Bedrooms int(2) unsigned ,
Region varchar(20) NOT NULL DEFAULT '' ,
SaleToLet int(1) unsigned ,
PRIMARY KEY (UserID)
);

Is it mySQL problem or something different

$insert = mysql_query("INSERT into userprofiles (UserID,
PropType,
PriceMin,
PriceMax,
Bedrooms,
Region,
SaleToLet) VALUES ('2', 'flat', '10', '100','1','leicester','1')"

that is the insert statement but it dont work ????

its weird

thanks in advance

sanj

 

[Bastien]

first off, always run a query like this. it makes it easier to debug
1. create the sql stateement in a variable like below

$sql="insert into...";

2. always include the db connection stuff ina separate file and include that file...only ever on connectio to write and maintain.
include("dbconnections"

3. always write the query like this:

$result=mysql_query($sql,$conn)or die ("failed because ".mysql_error());

then you get an error message telling you what the problem may be...

in your case, any integer value does not need quotes around the value...only text items need quotes..

so try this instead

$insert = "INSERT into userprofiles (UserID,
PropType,
PriceMin,
PriceMax,
Bedrooms,
Region,
SaleToLet) VALUES (2, 'flat', 10, '100',1,'leicester',1)";

$result=mysql_query($insert,$conn) or die (error is ".mysql_error());

this requires creating a separate db file like this:

<?php
//all code written and designed by Bastien Koert (

http://www.bastienkoert.com)

//distributed for use or alteration by all under the GPL
//this version released * march 3, 2003 * version 1.0
//
//check the password from the DB


$vusername = &quot;*****&quot;; //your username for you local system
$pwd =&quot;*****&quot;; //password to accecss mySQL
$host = &quot;localhost&quot;; //host is localhost - even for most web hosts
$dbname = &quot;somedb&quot;; //db name to be accessed



//connect to db
//$conn=mysql_connect($host, $username, $pwd) or die (&quot;Unable to connect to database&quot;

if (!($conn=mysql_connect($host, $vusername, $pwd))) {
printf(&quot;error connecting to DB by user = $vusername and pwd=$pwd&quot;
exit;
}
$db=mysql_select_db($dbname,$conn) or die(&quot;Unable to connect to database&quot;

?>

then simply include this file in the script
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[sanjdhiman]

thanks mate i will try that... i dont mind changing my code so that it works like u say. will try it out and hopefully it will work..

thanks

regards

sanj

 

[sanjdhiman]

Bastein

I am trying to write the include file, i already use an include file db.inc i call it.

Im trying to copy yours but was wondering why u commented out the line that start $conn.

And also is ur include file did u write a function so when u call the function like db_connect() it would know that you were tryin to connect to the database?

Thanks mate

Sanj

 

[Bastien]

no function though you could, if you wanted to..the second line's attempt to connect is better handled Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[sanjdhiman]

oi have tried to change the way i insert the sql values i.e. not putting ' ' round them but still nothing i even put the mysql_error() in he query but no error wsa returned but inspection of the table nothing changed???

u know wat that could be?

I have sat here for 2 days straight trying every single permutation i can think of but nothing

 

[Bastien]

you can connect right Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[sanjdhiman]

yea im connected and all. .the other inserts work but that last one just wont at all

 

[Bastien]

show the code for what doesn't work
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[sanjdhiman]

This is the function that i call

function register ($emailadd, $pword, $fname, $lname, $number, $street, $county, $citytown, $postcode, $telno, $workno, $faxno)
// register a new person to the database
// return true if successful and false if not

{

$connection = db_connect();

if (!$connection)
return &quot; Could not connect to database server, please try again&quot;;
// make sure user is not already entered in the database
$result = mysql_query(&quot;SELECT * FROM userlog WHERE username='$emailadd'&quot;
if (!($result))
return &quot;Could not execute query&quot;;
if (mysql_num_rows($result)>0)
return &quot;That email address is taken, go back and choose another one. &quot;;

//if all is well add user to database
//first add in the personal details first so that we can get the next new userID hence use that ID
//to match the userdetails to the userlogin details and the userattributes

$startdate=date(&quot;Y/m/d&quot;
$endtimestamp = strtotime(&quot;+90 days&quot;
$enddate=date('Y/m/d', $endtimestamp);

$getuserid = mysql_query(&quot;SELECT UserID FROM regusers WHERE Email= '$emailadd'&quot;
$row = mysql_fetch_array($getuserid);
$userid = stripslashes($row[&quot;UserID&quot;]);

$result = mysql_query(&quot;INSERT into regusers (FirstName,
LastName,
HouseNumber,
StreetName,
CityTown,
County,
Postcode,
DateRegistered,
EndOfRegistration,
HTelephone,
WTelephone,
Fax,
Email,
FlaggedDeleted,
AccountStatus) VALUES ('$fname', '$lname', '$number', '$street', '$citytown', '$county', '$postcode', '$startdate', '$enddate', '$telno', '$workno', '$faxno', '$emailadd', '0', 'Live')&quot;


$result = mysql_query(&quot;INSERT into userlog (UserID,
UserName,
Password) VALUES ('$userid', '$emailadd', '$pword')&quot;

$result = mysql_query(&quot;INSERT into userattr (UserID,
BgColour,
FgColour,
BorderColour,
FontSize,
FontFamily) VALUES('$userid', '0','0','0','0','0')&quot;


$result = mysql_query(&quot;INSERT into userprofiles (UserID,
PropType,
PriceMin,
PriceMax,
Bedrooms,
Region,
SaleToLet) VALUES('260','flat' ,'50000' ,'500000' ,'1' ,'name','1')&quot;



}
// ************************************************************************************************************


The last Insert on this does not work at all???

I have tried it with out the ' ' and with the ' ' on intergers etc..



This is the table structure


CREATE TABLE regusers (
UserID int(5) unsigned zerofill NOT NULL auto_increment,
FirstName varchar(50) NOT NULL DEFAULT '' ,
LastName varchar(50) NOT NULL DEFAULT '' ,
HouseNumber varchar(5) ,
StreetName varchar(50) NOT NULL DEFAULT '' ,
CityTown varchar(50) ,
County varchar(50) ,
Postcode varchar(10) NOT NULL DEFAULT '' ,
DateRegistered date ,
EndOfRegistration date ,
HTelephone varchar(15) ,
WTelephone varchar(15) ,
Fax varchar(15) ,
Email varchar(50) NOT NULL DEFAULT '' ,
FlaggedDeleted int(1) DEFAULT '0' ,
AccountStatus varchar(10) DEFAULT '0' ,
PRIMARY KEY (UserID),
KEY LastName (LastName),
UNIQUE UserID (UserID)
);


#
# Table structure for table 'userattr'
#

CREATE TABLE userattr (
UserID int(5) unsigned zerofill NOT NULL DEFAULT '00000' ,
BgColour int(10) unsigned ,
FgColour int(10) unsigned ,
BorderColour int(10) unsigned ,
FontSize int(3) unsigned ,
FontFamily int(3) unsigned ,
PRIMARY KEY (UserID)
);


#
# Table structure for table 'userlog'
#

CREATE TABLE userlog (
UserID int(5) unsigned zerofill NOT NULL auto_increment,
userName varchar(50) NOT NULL DEFAULT '' ,
password varchar(12) NOT NULL DEFAULT '' ,
LastLogged date NOT NULL DEFAULT '0000-00-00' ,
NoLogin int(4) unsigned NOT NULL DEFAULT '0' ,
PRIMARY KEY (UserID,UserID)
);


#
# Table structure for table 'userprofiles'
#

CREATE TABLE userprofiles (
UserID int(5) unsigned zerofill NOT NULL DEFAULT '00000' ,
PropType varchar(20) NOT NULL DEFAULT '' ,
PriceMin int(6) unsigned ,
PriceMax int(7) unsigned ,
Bedrooms int(2) unsigned ,
Region varchar(20) NOT NULL DEFAULT '' ,
SaleToLet int(1) unsigned ,
PRIMARY KEY (UserID)
);

 

[Bastien]

i don't see the error message here, the quotes are still around the numeric values...try

$result = mysql_query(&quot;INSERT into userprofiles (UserID,
PropType,
PriceMin,
PriceMax,
Bedrooms,
Region,
SaleToLet) VALUES(260,'flat' ,50000 ,500000 ,1 ,'name',1)&quot or die (&quot;error is &quot;.mysql_error()); Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[sanjdhiman]

I have tried that as well, i have tried many permutations and combinations of the quotes. But no error message either. Its weird right
and still no insert into table. It works on another seperate file, that is if i write the insert on a new web page and then do it .. it works fine its when i bring it into this function it dont?

does that help further#???
regards

sanj
thanks for looking into the problem

 

[KempCGDR]

Is there anything in the variable you are trying to write to UseriD? If so, is it something that is already in the table? If it is blank or a duplicate then it won't let you insert it because UserID is meant to be unique.

Looking at your code, you have set the value for it, which means that after running the query once it'll fail because the value 260 already exists.

 

[binky]

sanjdhiman,

Another thought to try. Have you double checked the permissions of the username you are connection with to MySQL? And if you have changed the permissions have you reloaded MySQL?

HTH Binky :
><>