Insert

[nova]

Alright, im just beggining to learn SQL. I already know PHP, and figured out pretty well how to read from MySQL, but now need to figure out how to write to a database and am getting stuck. Here is the code i got for inserting a new row:

$insertQuery = "INSERT INTO region VALUES
                         (NULL, ".
                         "\"" . $regionName . "\", " .
                         "\"" . $description . "\", ".
                         "NULL)";

Alright, i understand i am writing to database 'region' and inserting $regionName into column 2, and $description into column 3. But what are all the "\"". ?
I also understand that NULL will make the database write a consecutive 1st row, so like 1, 2, 3, 4, 5, ect. so how do i name the 1st row whatever i want, like $user?

Thanks'

[MrAlaska]

The backslashes escape the quotes, it is much cleaner to just use single quotes. Here is the same query:

$insertQuery = "INSERT INTO region
VALUES
(NULL, '$regionName', '$description', NULL)";

That query requires a field for every value, your code is better in the long run to address the fileds explicitly so you don't get burnt if you add a column later:

$insertQuery = "INSERT INTO region
(id,name,desc,extra)
VALUES
(NULL, '$regionName', '$description', NULL)";

(I am, of course, taking wild guesses as to what your actual column names might be)

The automatic sequencing of the id column is set up when you build your table and assign 'auto_increment' to the row

(adding) If you have auto_increment and address the fields explicitly, you do not need to do anything to get it to increment. You can just ignore that column and let MySql take care of it. Null values are generally discouraged unless you need them for some reason, a table will run more efficieantly if you asign NOT NULL default='' to each column when you create your table.

[nova]

k, got that to work, thank you, but i cant seem to get mysql_fetch_array to work...

$result = mysql_query("SELECT * FROM codersarch", $connection);
$row = mysql_fetch_array($result);

foreach ($row as $subrow) {
print"$subrow <BR>";
}

What is wrong there? This is the array i get:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/nova/public_html/TEST/signup.php on line 11

Warning: Invalid argument supplied for foreach() in /home/nova/public_html/TEST/signup.php on line 13

[MrAlaska]

For some reason your $result is empty, echoing back the error might divulge a reason

$sql="SELECT * FROM codersarch";
$result = mysql_query($sql, $connection)
    or die ("<p>$sql<p>".mysql_error());

(something like that)

EDIT, I have a sneaking suspicion it will throw an error like 'column codersarch does not exist.' Check your spelling, but more important check my spelling!

[nova]

actualy, i never created the table. hm.. how do i go about doing that? 🙂

[nova]

i have the code but i cant figure it out... what i want is this:

Name | Password
---------|--------------
user | password

etc. Just i want the table to be empty when its created.

[MrAlaska]

You gotta have a table to query on l

The SQL to create a table would look something like this:

create table member(
  member_id int unsigned auto_increment NOT NULL,
  name varchar(20) NOT NULL default '',
  password varchar(20) NOT NULL default '',
  PRIMARY KEY (member_id)
  )
;

You might do well read up a little at the mysql site, their documentation includes an entry level tutorial

http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html#Tutorial

Other people might recommend an administration tool like PhpMyAdmin for creating tables and such. I have no experience in that aspect.

[Mega]

while( $row = mysql_fetch_array( $result ) ){
echo $row['id'];
}

is my prefered method of doing things 🙂

[MrAlaska]

Originally posted by Mega
while( $row = mysql_fetch_array( $result ) ){
echo $row['id'];
}

is my prefered method of doing things 🙂

I kinda grew fond of the while loop, also, but if I am reading the results on this page correctly:
http://phpxpath.sourceforge.net/benchmark/phpBench.php
it would appear I might hafta readjust my preferances, at least for read only loops.