Simple problem driving me crazy!!!


#1

I’m new at php but have gotten fairly familiar with it over the past few weeks. I hired a programming firm to get my business started and have been modifying the code I purchased to meet my ongoing needs. So far, so good, but I am having a problem with one of my applications when I try to write to my database.

I’m using the INSERT INTO table (columns) VALUES (values) command, and the problem is that everytime I try to put a new value in for the next column of an existing row, I end up writing a whole new row into the table and this won’t work for my application…

My code looks like this:
$newweeklyscore = “INSERT INTO weeklyscores (teamname,teamlevel,week$weektoget) VALUES (’$teamname’,’$teamlevel’,’$totalweeklypts’)”;
$result = mysql_query($newweeklyscore);

If anyone can help, please let me know as soon as possible since I am up against a very tight deadline!!! THANK YOU!!!

CV


#2

Using the INSERT query, adds a new row. To update an exisiting row, use the UPDATE function.

$newweeklyscore = "UPDATE weeklyscores SET teamname='$teamname',teamlevel='$teamlevel',week$weektoget='$totalweeklypts' WHERE something = something";

Notice the end part of the query. I would assume each row has a unique id number? If so you should assign one using an auto_increment field. This allows you to update only one specific row. If you dont have this, then all rows will be updated at one time.

:)

#3

Hey Carella… I’m curious. Up til now, I’ve been generating unique IDs for my … well for anything that needs unique IDs … by having a separate “IDs” table that was called and incrimented whenever I wanted to get a new ID. What is this auto-increment type you speak of? It could come in VERY handy.


#4

Hi Tarential,

The auto increment function is indeed very useful. It makes it very easy to update a specific row as each row will have a unique id number. The auto_increment field should always be your primary key, as no two rows can be the same.

So, lets say for the sake of argument, your table schematic is as follows:

CREATE TABLE tablename (
    name varchar(150) NOT NULL,
    address varchar(150) NOT NULL,
    comments text,
    PRIMARY KEY (name)
);

This stores info posted from a form. Name, Address, Comments.

To this you would add the following:

id int(4) NOT NULL auto_increment,

So, your new schematic is as follows:

CREATE TABLE tablename (
    id int(4) NOT NULL auto_increment,
    name varchar(150) NOT NULL,
    address varchar(150) NOT NULL,
    comments text,
    PRIMARY KEY (id)
);

So, lets say someone posts some data. Your sql syntax would be something like:

$query = "INSERT INTO tablename (name,address,comments) VALUES ('$name', '$address', '$comments')"; $result = mysql_query($query) or die(mysql_error());

Notice that you don’t add anything for the id field. This increments automatically. When you view the schema for your database, you’ll see the id is there automatically. So your first row will have the id value of 1, the 2nd of 2 and so on. Each row has a different id.

View your rows by running a query and you`ll see what I mean.


$query = "SELECT * FROM tablename";
$result = mysql_query($query) or die(mysql_error());

while ($row=mysql_fetch_array($result))

{

  echo $row['name'] . "<br>n";
  echo $row['address'] . "<br>n";
  echo $row['comments'] . "<br>n";
  echo $row['id'] . "<br>n";

}

You would then use this id to update a certain row. Pull the data from your database and store the value of id in a hidden form field. When the info gets posted use this id to change a specific row. You can also pull a certain row from the database using the WHERE query.

$query = “SELECT * FROM tablename WHERE id = ‘2’”;

There is no need to create id numbers yourself when mysql can do it for you.

Check the mysql website for more information. Hope this made some sense.

:)

#5

Hell of a post, but I’m no newbie. All you needed to post was this:

Thanks anyway, though.


#6

Hello carella:

Just wanted to thank you much for the quick reply and helpful advice. Haven’t had the chance to try it yet, but it looks like it’ll do the trick. Just two other quick questions:

  1. My rows don’t have unique ID numbers, the qualifier is essentially the Team Name. Therefore, I assume I won’t need to Auto-Increment, since the “weekly score” will only be written to the one row belonging to the Team?? Just wanted to make sure that I could leave out the “something=something” clause since I’m defining the Team Name when the table is updated…

  2. Also, is there a way to Time Stamp an entry into the database with the php code?

Thanks again!!! I appreciate your help!
CV


#7
Hell of a post, but I'm no newbie.

LOL! Well, I wasn’t too sure. As its the beginners section, a detailed explanation is useful to newbies anyway. :)

ilovethisstuff, so long as you can identify each individual row, then thats fine, you won`t need the auto_increment. It just makes it easier.

With regards to a timestamp, well you can do this a number of ways. For instance you can simply create an additional varchar field in your database and use the PHP date format, like this:

$date = date(“D j M Y, G:ia”);

In this instance $date would display as follows:

Wed 21 Apr 2004, 21:42pm

For more info on the date() function, and other alternatives, check out PHP.net.


#8

Hey carella,

The UPDATE code you offered worked like a charm!! Thank you again!
Moving on to the Time & Date stamp. I’ll let you know…

Thanks
CV


#9

Sure, no problem. We are all here to help each other. :)


#10

carella and ilovethisstuff:

I use timestamps A LOT and have found that inserting just time() works best (and as I was recently corrected, making it an int)

You can compare UNIX time stamps much easier and as an int, use comparison functions in your MySQL calls. Plus it’s easy in displays to just do a date("l, F jS, Y g:i a, $time) for a nice, long formatted output and then a date(“m-d-y”, $time) for a short, generalized output for multicolumn form outputs that wouldn’t be able to fit a longer format.