Trouble with INSERT

keith73

I've searched the forum but didn't find my answer.

I'm new to PHP/MySQL.

Here's some code:

$connection=mysql_connect("HOSTIP","USERNAME","PWD"); // actual values not shown for obvious reasons.

print "Welcome" . $REMOTE_USER . "<br>\n";
$timestamp = date("U");
$dateinfo = getdate($timestamp);
$query = "INSERT into userlog VALUES ('$REMOTE_USER','index.php','$dateinfo[mon]','$dateinfo[mday]','$dateinfo[year]','$dateinfo[hours]','$dateinfo[minutes]','$dateinfo[seconds]')";
$result = mysql_query($query);
print "<br>\n<b>Result:</b><i>" . $result . "</i><br>\n";

mysql_close($connection);

When I check to see if the information was put into the database, I get nothting, the Table has 0 rows.
I've verified that I can connect to the database and that I can fetch the field names so I know it's not a connection issue.

I get no errors but nothing is inserted.

Any help is always appreciated,

Keith

marcel

I didn't work last time with mysql.
But ...if I'm not wrong you have to select a database, as long as in mysql_connect is nothing about db name...

check mysql_select_db(...)

In any caseyou should get an error/warning...

check in your php.ini about `error_reporting' paramater

or at least (if you can not access php.ini) do a phpinfo() into a PHP page and search for `error_reporting' to see the value .

requiem

I couple of things I noticed:

First of all - why are you inserting the date like that? The year, month, day, hour, minute, second in separate fields? Just use the "datetime" type in MySQL - much much easier and far more powerful when it comes to date comparisons later on. Then you could just do:

INSERT INTO userlog VALUES ('$REMOTE_USER','index.php',now())

Anyway onto your question - first of all here is how I'd trouble shoot this.

1) Output your full query string ($query) before putting it into MySQL (so just before the result line would be good). Then copy and paste the SQL you've got in your browser into a console MySQL session - if there is a SQL error, you'll see it now.

2) Compare what you are trying to insert with what the SQL columns are allowed to take. Are you stuffing a varchar into an integer? or perhaps missing out a unique column that was required? Without seeing the format of the SQL table I can't help much further.

Good luck!

Cheers,

Rich

keith73

DOHT!!

I did use the mysql_select_db on another page, and forgot to use it on this one. I added it in there and what do you know, it worked.

Thanks,

Keith

keith73

Turns out I forgot the mysql_select_db.

As for the date. I need to be able to customize queries so that it can match users to month or date, or hour.

i.e. list users that accessed the page between 4-5pm in the month of December.

I thought it would be more robust keeping the fields separate. (I'm used to programming in PERL and that always worked for me there.) If there is an easier way, I'm all for it. I'll try it out.

Thanks for the input,

Keith

requiem

Hi,

Sure there's an easier way.

Use the "datetime" type and then you can use MySQL commands such as:

(Here is my MySQL table first)

CREATE TABLE users (userid int not null auto_increment, name varchar(50), logindate datetime, primary key(userid));

now the select statement...

SELECT * FROM users WHERE now() < DATE_ADD(logindate, INTERVAL 20 MINUTE)

It might read strangely, but what it will do is bring back all users where logindate (a datetime row within my table) is 20 minutes (or greater) than now() (the current system time) - i.e. all people who've logged-in in the past 20 minutes.

or another example:

SELECT * FROM users WHERE SECOND(logindate) = 30 AND MONTH(logindate) = 1

This is a little more abstract! But what it does is list all users who logged in during the month of January at exactly 30 seconds past any minute of any hour!

So you can see - using SQL commands such as SECOND, MINUTE, HOUR, DAYNAME, YEAR, etc is so much easier than selecting loads of debris from the database that you don't really want, only to then have to parse it all and check it manually.

See the SELECT chapter of the MySQL manual for further information (or reply to this!)

Cheers,

Rich