Two Questions. MySql query and timestamps

@ConorApr 03.2004

Question 1. Any idea why this query wont work?

[code=php] $linkid= mysql_connect(“localhost”, “”, “”)or die(mysql_error()); mysql_select_db(“”, $linkid); $sql = ‘INSERT INTO `blog` ( `ID` , `timestamp` , `title` , `content` ) ‘ . ‘ VALUES ( ”, NOW( ) , $title, $content );’ . ‘ ‘; $query=mysql_query($sql); echo”Entry Added!”; mysql_close($linkid); [/code]

Question 2.

How do i convert a timestamp like this 20040403173538 to a regualr date format

to post a comment

PHP

52 Comments(s) _↴

@JonaApr 03.2004 — #[font=arial]Answer 2 is easier, so I'll answer that first. Use the [url=http://php.net/date]date()[/url] function.[/font]

[code=php]
 $currTime = time();
 echo (date("m/d/y g:i a", $currTime));
 [/code]

@JonaApr 03.2004 — #[font=arial]For question 1, you might try this... What does the error say?[/font]

[code=php]
 $linkid = mysql_connect("localhost", "", "") or die(mysql_error());
 mysql_select_db("", $linkid);
 [/code]

$sql = "INSERT INTO <span><code>blog</code></span> ( <span><code>ID</code></span> , <span><code>timestamp</code></span> , <span><code>title</code></span> , <span><code>content</code></span> ) "
 . " VALUES ( \'\', NOW( ) , $title, $content )";

[code=php]
 $query=mysql_query($sql);
 if(!$query) die(mysql_error());
 echo "Entry Added!";
 mysql_close($linkid);
 [/code]

@ConorauthorApr 03.2004 — #that basically worked just had to put '' around the $title and $content variables thanks.

@JonaApr 03.2004 — #[font=arial]Welcome. (I think that was for question #2, right? ?)[/font]

@ConorauthorApr 03.2004 — #no the 1st question about insertion. The second one ill try right now. Im developing a simple blog for myself.

any idea why right now is coming up as 01/18/38 10:14 pm

@JonaApr 04.2004 — #[i]Originally posted by RefreshF5 [/i]

[B]any idea why right now is coming up as 01/18/38 10:14 pm [/B][/QUOTE]

[font=arial]It's the date and time of the server, not your local time.[/font]

[b][J]ona[/b]

@ConorauthorApr 04.2004 — #ahh ok. Well i sorted that out and now i just have one more question.

Im trying a page like this http://ryanbrill.com/archives.php but without the year seperation. IS this hard to do?

@JonaApr 04.2004 — #[i]Originally posted by RefreshF5 [/i]

[B]Im trying a page like this http://ryanbrill.com/archives.php but without the year seperation. IS this hard to do? [/B][/QUOTE]

[font=arial]No, not necessarily, but it depends how you're doing it. It may be just reading the subdirectories of the archives directory, and putting them into a list - which is not hard to do at all.[/font]

[b][J]ona[/b]

@ConorauthorApr 04.2004 — #Well the way i want to do it is have an output.php file which would output the articles like output.php?ID=2 and then in the archive.php i would want it to list the the articles so the links would link to output.php?ID=id

ok i got the output.php file worked out now i just need to get the archive.php file to list all the links

@JonaApr 04.2004 — #[font=arial]I would include files, and then read the list of them in a subdirectory. Unless you're using a database, but it really isn't necessary, and is probably more work.[/font]

[b][J]ona[/b]

@ConorauthorApr 04.2004 — #yeah i am using a database. Basically all this is displaying links to the blog entries but on there own.

@ConorauthorApr 04.2004 — #got it all worked out. Wasnt all that difficult.

@JonaApr 04.2004 — #[font=arial]Glad you got it working![/font]

@ConorauthorApr 04.2004 — #yeah i just used a for loop to echo it all out

[code=php]
 for ($y = 0; $y < $rows; $y++)
 {
 $ID = mysql_result($result, $y, 'ID');
 $title = mysql_result($result, $y, 'title');
 $content = mysql_result($result, $y, 'content');
 $date = mysql_result($result, $y, 'date');
 echo' <a href="entry.php?ID='.$ID.'">';
 echo' '.$title;
 echo' </a>';
 }
 [/code]

now comes the task of trying to add user commenting. SOmething i really have no idea where i will start at.

@ConorauthorApr 05.2004 — #I realize that the comments table will have to be a seperate table from the blog table but how does it know which comments goes to which entry. ?

@JonaApr 05.2004 — #[i]Originally posted by RefreshF5 [/i]

[B]I realize that the comments table will have to be a seperate table from the blog table but how does it know which comments goes to which entry. ? [/B][/QUOTE]

[font=arial]Create a row called something like "replyto" and fill it in with the id number of the blog post. You could also go by name.[/font]

@ConorauthorApr 05.2004 — #ahh so when a user clicks Add comment it will go to a page and the replyto will be the Id of the one its commenting on and then in comment query ill do where"SELECT * FROM blog WHERE replyto='".$ID."'";

and $ID would be the ID row from the blog table

am i on the right track?

@JonaApr 05.2004 — #[font=arial]Sounds like it. ? [/font]

@ConorauthorApr 05.2004 — #awesome ill go play around with it.

@ConorauthorApr 05.2004 — #any idea whats wrong with this

[code=php]
 $ID = $_GET['ID'];
 $query = "SELECT * FROM blog WHERE ID='".$ID."'";
 $result = mysql_query($query);
 $id=mysql_result($result,0,'ID');
 $title = mysql_result($result, 0, 'title');
 $content = mysql_result($result, 0, 'content');
 $date = mysql_result($result, 0, 'date');
 [/code]

mysql result works there but in this here it doenst work

[code=php]
 $query2 = "SELECT * FROM comments WHERE replyto='".$id."'";
 $result2=mysql_query($query2);
 $name= mysql_result($result2, 0, 'name');
 $comment= mysql_result($result2, 0, 'comment');
 [/code]

for the $id variable i also tried

[code=php]
 $id=$_POST['ID'];
 [/code]

Heres my error

Warning: mysql_result(): Unable to jump to row 0 on MySQL result index 5 in /home/benson1/public_html/nucleus/entry.php on line 34

Warning: mysql_result(): Unable to jump to row 0 on MySQL result index 5 in /home/benson1/public_html/nucleus/entry.php on line 35

@JonaApr 05.2004 — #[font=arial]Did you end the previous query first?[/font]

@ConorauthorApr 05.2004 — #i was not aware that you had to end it? how would i do that?

@JonaApr 05.2004 — #

[code=php]
 mysql_free_result($result);
 [/code]

@ConorauthorApr 05.2004 — #nope that didnt do it.

@JonaApr 05.2004 — #

[code=php]
 $query2 = "SELECT * FROM comments WHERE replyto='".$ID."'";
 [/code]

[font=arial]I think you forgot to capitalize the variable. ?[/font]

@ConorauthorApr 05.2004 — #no i have to variables

$id and $ID

ive tried using both and both produce the same error.

@JonaApr 06.2004 — #[font=arial]Try using 5 instead of 0 on your second set of mysql_result functions.[/font]

@ConorauthorApr 06.2004 — #nope now it just says unable to jump to row 5 instead of 0

@JonaApr 06.2004 — #[font=arial]Strange, I've never gotten that error before. ?

Could you post some more code, please?[/font]

@ConorauthorApr 06.2004 — #heres my whole file before i echo it out

[code=php]
 <?
 $un = "";
 $pass = "";
 $db = "";
 $host = "localhost";
 $sql = mysql_connect($host, $un, $pass) or die(mysql_error());
 mysql_select_db($db, $sql) or die(mysql_error());
 if (mysql_ping($sql))
 {
 $ID = $_GET['ID'];
 $query = "SELECT * FROM blog WHERE ID='".$ID."'";
 $result = mysql_query($query);
 $id=mysql_result($result, 0, 'ID');
 $title = mysql_result($result, 0, 'title');
 $content = mysql_result($result, 0, 'content');
 $date = mysql_result($result, 0, 'date');
 mysql_free_result($result);
 
 $query2 = "SELECT * FROM comments WHERE replyto='".$id."'";
 $result2=mysql_query($query2);
 $name= mysql_result($result2, 0, 'name');
 $comment= mysql_result($result2, 0, 'comment');
 mysql_free_result($result2);
 
 }
 ?>
 [/code]

i know its retreiving the id because i echoed it out ?

@ConorauthorApr 06.2004 — #got it workked out, it seems even though i was uploading it it wasnt uploading, i dont know but i went today and the error was gone. Now the insert isnt updating grrr...

Now i have everything working but theres one problem. After the user adds a comment. Everytime they refresh it adds the comment over and over again anyway to fix this?

@JonaApr 06.2004 — #[i]Originally posted by RefreshF5 [/i]

[B]Everytime they refresh it adds the comment over and over again anyway to fix this? [/B][/QUOTE]

[font=arial]Have it display a message, while it redirects to the page that lets them view the posted comments.[/font]

@ConorauthorApr 06.2004 — #not quite understanding what you mean? are you saying process the entry on another page?

@ConorauthorApr 06.2004 — #i think the best sollution would be to make it so they can only comment once every however many seconds, how would i do this/

@JonaApr 06.2004 — #[font=arial]Add two more fields to your comments table: ip and lastpost. When they post a comment, find a row with their IP in it, and update the corresponding lastpost field with time() - if one doesn't exist, add another row with their IP and fill lastpost with time(). Before that, though, look for their IP in the database, and if it exists in a row, select the corresponding lastpost time, and subtract it from time(). If the difference is less than sixty (seconds), display an error saying "Double-post attempt" or something, and don't post.[/font]

@ConorauthorApr 06.2004 — #?

would it be something like this?

[code=php]
 $postlimit=$row['time'] - time();
 $ip=$_SERVER['REMOTE_ADDR'];
 if(($ip==$row['ip']) AND ($postlimit<=60))
 {
 echo"You must wait 60 seconds before posting again";
 }
 else
 {
 $query="INSERT INTO comments ( ID , name , comment , replyto , title , date , ip , lastpost) VALUES ('', '$user', '$comments', '$id' , '$title' , '$dat' , '$ip' , '$time');"; 
 mysql_query($query);
 }
 [/code]

if not im lost

@JonaApr 07.2004 — #[font=arial]Yes, that's what I was saying.[/font]

@ConorauthorApr 07.2004 — #what would the query look like something like this

[code=php]
 $query = "SELECT * FROM comments WHERE ip='".$_SERVER['REMOTE_ADDR']."LIMIT 1";
 [/code]

the only problem with that though is if there is no id that equals the current users?

@ConorauthorApr 07.2004 — #ok i tired this but got an error

[code=php]<?
 $query3 = "SELECT * FROM comments WHERE ip='".$_SERVER['REMOTE_ADDR']."LIMIT 1";
 $result3=mysql_query($query3);
 while ($rows = mysql_fetch_row($result3)) 
 { 
 $lastreply=$rows[8];
 $ip_add=$rows[7];
 echo $postlimit;
 echo $ip_add;
 }
 [/code]

My error

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/benson1/public_html/nucleus/entry.php on line 67

also tried

[code=php]
 $query3 = "SELECT * FROM comments WHERE ip='".$_SERVER['REMOTE_ADDR']."LIMIT 1";
 $result3=mysql_query($query3);
 while ($rows = mysql_fetch_assoc($result3)) 
 {
 $lastreply=$rows['lastpost'];
 $ip_add= $rows['ip'];
 
  echo $postlimit;
  echo $ip_add;
 
 }
 [/code]

with basically the same error

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/benson1/public_html/nucleus/entry.php on line 67

@JonaApr 07.2004 — #[font=arial]This will avoid the error.[/font]

[code=php]
 $query3 = "SELECT * FROM comments WHERE ip='".$_SERVER['REMOTE_ADDR']."LIMIT 1";
 $result3=mysql_query($query3);
 if($result3){
 while ($rows = mysql_fetch_assoc($result3)) 
 {
 $lastreply=$rows['lastpost'];
 $ip_add= $rows['ip'];
 
  echo $postlimit;
  echo $ip_add;
 
 }
 }
 [/code]

@ConorauthorApr 07.2004 — #yup that did it thanks, so this should work

[code=php]
 <?
 $query3 = "SELECT * FROM comments WHERE ip='".$_SERVER['REMOTE_ADDR']."LIMIT 1"; 
 $result3=mysql_query($query3); 
 if($result3){ 
 while ($rows = mysql_fetch_assoc($result3)) 

 { 
 $lastreply=$rows['lastpost']; 
 $ip_add= $rows['ip']; 
 
  echo $postlimit; 
  echo $ip_add; 
 
 } 
 }
 $postlimit=$lastreply - time(); 
 $ip=$_SERVER['REMOTE_ADDR']; 
 if(($ip==$ip_add]) AND ($postlimit<=60)) 
 { 
 echo"You must wait 60 seconds before posting again"; 
 } 
 else 
 { 
 $query4="INSERT INTO comments ( ID , name , comment , replyto , title , date , ip , lastpost) VALUES ('', '$user', '$comments', '$id' , '$titl' , '$dat' , '$ip' , '$time');"; 

 mysql_query($query4); 
 }
 ?>
 [/code]

@JonaApr 07.2004 — #[font=arial]Looks like it should work![/font]

@ConorauthorApr 07.2004 — #nope still adding one on each refresh ?, i wonder how pyro does it on his site.

i think i know why i tried echoing my variables and nothing came up so since its not retrieving the variables its just performing the else statement hmm...

@JonaApr 07.2004 — #[i]Originally posted by RefreshF5 [/i]

[B]nope still adding one on each refresh ?, i wonder how pyro does it on his site. [/B][/QUOTE]

[font=arial]I'm sure he does it the same way, there's just something wrong with your code that we've got to fix for it to work as well.

Here's how I did mine, although it's in part of a forum so it doesn't go by IP address, it goes by username.[/font]

[code=php]
 $query = "SELECT last_post_time FROM players WHERE username = '". $_SESSION["username"] ."'";
 $result=mysql_query($query);
 if(!$result){dispErrMsg("Database error: ".mysql_error());}
 if(mysql_num_rows($result)==0){ $lastPostTime="never"; }
 $row=mysql_fetch_assoc($result);
 $lastPostTime=$row["last_post_time"];
 
 if($lastPostTime > time()-60){
 dispErrMsg("Flood Detected!");
 }
 [/code]

[font=arial]Does that help any?[/font]

@ConorauthorApr 07.2004 — #and i finally got it working thanks jona

[code=php]
 $query3 = "SELECT lastpost FROM comments WHERE ip = '". $ip."'"; 

 $result=mysql_query($query3); 

 if(!$result){echo"Database error"; 
 }
 if(mysql_num_rows($result)==0)
 { 
 $lastPostTime="never"; 
 }
 
 $row=mysql_fetch_assoc($result); 

 $lastPostTime=$row["lastpost"]; 
 
 if($lastPostTime > $time-60){ 

 echo"You can only reply once every 60 seconds"; 
 } 
 elseif(($comments != "") AND ($title != ""))
 { 
 $query4="INSERT INTO comments ( ID , name , comment , replyto , title , date , ip , lastpost) VALUES ('', '$user', '$comments', '$id' , '$titl' , '$dat' , '$ip' , '$time');"; 

 mysql_query($query4); 
 
 } 
 elseif(($comment=="") AND ($title==""))
 {
 echo"You must fill in all form fields";
 }
 echo"</div></form>";
 include("footer.php");
 ?>
 [/code]

@JonaApr 07.2004 — #[font=arial][color=blue]Contargulations! ? [/color][/font]

@ConorauthorApr 07.2004 — #thanks again now i just need to write up some CSS for my blog and i can start my next project, a text based game that goes by user radio button choices

@akadisApr 07.2004 — #ok stupid question, what does your script do?

@JonaApr 07.2004 — #[i]Originally posted by akadis [/i]

[B]ok stupid question, what does your script do? [/B][/QUOTE]

[font=arial]He created a blog system. I'd suggest learning to code on your own, by the way, rather than copying other people's code; or use a professionally made application such as [url=http://www.ryanbrill.com/products/xblogpro/]xBlog Pro[/url] or [url=http://www.movabletype.com/]MovableType[/url].

Also, I noticed in your signature that you would like suggestions for how your site could be better designed. Would you mind posting something like "Critique my site" in the General forum, and then PM me when you have done so? I would be glad to help you learn to design in CSS and have a better all-round layout. ?[/font]

@JonaApr 07.2004 — #[i]Originally posted by RefreshF5 [/i]

[B]thanks again now i just need to write up some CSS for my blog and i can start my next project, a text based game that goes by user radio button choices [/B][/QUOTE]

[font=arial]Good luck. By the way, usually games are more successful if they're interactive, so in the long term, you might want to use a cross between PHP and Flash. ? Just a suggestion, though.[/font]

@akadisApr 07.2004 — #thanks for the reply, its just as a begining programer i think its good to learn/read other's code as well

@JonaApr 07.2004 — #[i]Originally posted by akadis [/i]

[B]thanks for the reply, its just as a begining programer i think its good to learn/read other's code as well [/B][/QUOTE]

[font=arial]No problem, man, I was just letting you know that it's just not always a good idea to try to use someone else's code exactly, because it's probably not very portable - professional applications almost always are (with reason).

Also, if you didn't notice it, I edited my previous post - I'd like to to take a look at it. ?[/font]

Also in #PHP _↴

Random 6 digit number mail() - "sendmail_from" not set in php.ini Perform PHP script and then redirect

Success!

Help @Conor spread the word by sharing this article on Twitter...

Tweet This

Two Questions. MySql query and timestamps

52 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Two Questions. MySql query and timestamps

52 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

52 Comments(s) _↴

Also in #PHP _↴