php and mysql insert...where making me tear my hairs out!!! help!!!

[kodiak]

ok, so i need to insert something to my mysql database. I have seperately tested the connection, and it's good. It's just something wrong with my code. Look at it.

<?php
include("db.php");
$link2=mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
if(!$link2){ echo "could not connect.";}

$myusername="test";
$mypassword="test";
$sql="insert into `members` (email) VALUES ('[e-mail]')  WHERE username='$myusername' and password='$mypassword' ";
$result=mysql_query($sql);
$count=mysql_num_rows($result);
if($result)
{
echo "successful.";
}
?>

It's not some internal problem with the WHERE function, because it works fine when i use it with other things..

this is very simple, but it simply wont work... help is greatly appreciated!!!!

~kodiak

[Cerberus]

Don't you have to pass the db connection variable in?

mysql_select_db("$db_name", [B]$dbconnect[/B]);

$result=mysql_query($sql, [B]$dbconnect)[/B];

[kodiak]

uhh no..

[Ezzaral]

Does the query work fine from a SQL tool, like phpMyAdmin or such? I've not seen table names surrounded with the "`" before, but that may be unrelated. Are there non-nullable fields that you are not including in your insert? If you don't want to try it out in a SQL tool you could try

$result=mysql_query($sql) or die("SQL Error".mysql_error());

to see what it is complaining about.

[kodiak]

hmm.... i did what you said and got this error

SQL ErrorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE username='test' and password='test'' at line 1

im kinda new to sql...
so why are there two single quotes after test???
I have no clue... maybe that has something to do with it...

[jbennet]

here is some correct code (its not for your scenario but it should show you how to do things properley)

<?php

  $host = 'localhost';
  $user = 'youruser';
  $pass = 'yourpassword';

$connection = mysql_connect($host, $user, $pass) or die ('Unable to connect!');

 mysql_select_db('databasename) or die ('Unable to select database.');

$query = "SELECT * FROM items";

  $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

?>

[Ezzaral]

Oh! hehe I just re-read the SQL and can't believe I missed that before. Insert statements do not have WHERE clauses. Are you trying to insert a new record or update an old one? Update code would be

UPDATE members SET email='$email' WHERE username='$username' and password='$password'

[kodiak]

Ugghh... it still doesn't work!!! I don't understand!!! No error messages come, but it wont update the fields!!!

[kodiak]

ok wait.....

when i added the fields, I added an auto_increment id, which made the first user have an id of 1, second have an id of 2, etc. I made it the primary key. When i tried this it worked.

<?php
include("db.php");
session_start();

$link=mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$myusername= "john";
$mypassword= "1234";
$m1=0;
$m1 = $_SESSION['m1'];


if(!session_is_registered(myusername)){
die("Woops! Your session has ended, which means you need to log in again, or you didn't complete the challenge! Go back, log in and complete the challenge again.");
}

if(session_is_registered(myusername)) {
$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql) or die("SQL Error".mysql_error());

}

if($result){
echo "successful.";
}

mysql_close($link);
$email="john";
$m1=1;
$link2=mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
if(!$link2){ echo "could not connect.";}


$sql="UPDATE members SET m1='$m1' WHERE id='1'";
$result=mysql_query($sql) or die("SQL Error".mysql_error());
$count=mysql_num_rows($result);
if($result)
{
echo "num 2 succesful. ";
echo $m1;
echo $myusername;
echo $mypassword;
}

?>

when i did that it worked.... so what should i do??
change the primary key to username or password, and the do that???

im off to try it...

[kodiak]

it doesn't work..... i used this code for the tables...

CREATE TABLE `members` (
`id` int(4) NOT NULL auto_increment,
`username` varchar(65) NOT NULL default '',
`password` varchar(65) NOT NULL default '',
`email` varchar(65) NOT NULL default '',
`score` int(65) NOT NULL default '0',
`m1` int(65) NOT NULL default '0',
`m2` int(65) NOT NULL default '0',
`m3` int(65) NOT NULL default '0',
`m4` int(65) NOT NULL default '0',
`m5` int(65) NOT NULL default '0',
`m6` int(65) NOT NULL default '0',
`m7` int(65) NOT NULL default '0',
`m8` int(65) NOT NULL default '0',
`m9` int(65) NOT NULL default '0',
`m10` int(65) NOT NULL default '0',
`m11` int(65) NOT NULL default '0',
`m12` int(65) NOT NULL default '0',
`m13` int(65) NOT NULL default '0',
`m14` int(65) NOT NULL default '0',
`m15` int(65) NOT NULL default '0',
`m16` int(65) NOT NULL default '0',
`m17` int(65) NOT NULL default '0',
`m18` int(65) NOT NULL default '0',
`m19` int(65) NOT NULL default '0',
`m20` int(65) NOT NULL default '0',
PRIMARY KEY (`username`),
);

and got this error...

#1064 - 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 28

[kodiak]

i figured something out....
i didn't need a comma after the primary key part.... so i did this...

CREATE TABLE `members` (
`id` int(4) NOT NULL auto_increment,
`username` varchar(65) NOT NULL default '',
`password` varchar(65) NOT NULL default '',
`email` varchar(65) NOT NULL default '',
`score` int(65) NOT NULL default '0',
`m1` int(65) NOT NULL default '0',
`m2` int(65) NOT NULL default '0',
`m3` int(65) NOT NULL default '0',
`m4` int(65) NOT NULL default '0',
`m5` int(65) NOT NULL default '0',
`m6` int(65) NOT NULL default '0',
`m7` int(65) NOT NULL default '0',
`m8` int(65) NOT NULL default '0',
`m9` int(65) NOT NULL default '0',
`m10` int(65) NOT NULL default '0',
`m11` int(65) NOT NULL default '0',
`m12` int(65) NOT NULL default '0',
`m13` int(65) NOT NULL default '0',
`m14` int(65) NOT NULL default '0',
`m15` int(65) NOT NULL default '0',
`m16` int(65) NOT NULL default '0',
`m17` int(65) NOT NULL default '0',
`m18` int(65) NOT NULL default '0',
`m19` int(65) NOT NULL default '0',
`m20` int(65) NOT NULL default '0',

PRIMARY KEY (`username`)
);

heres the error i get:

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

EDIT - I think i figured it out, i just had to take away the auto increment id thingy...

now let me go see if it will work with php..

[kodiak]

figured it out!!! got i all working correctly, thanks to the people that helped!!!