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 ('asdf@asdf.com')  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

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];

uhh no..

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.

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

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());

?>

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'

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

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

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

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.