Checking MySQL for entry?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
angelicodin
Forum Commoner
Posts: 81
Joined: Fri Nov 13, 2009 3:17 am
Location: Oregon, USA

Checking MySQL for entry?

Post by angelicodin »

Hey ya guys. I'm still learning php and sql but I'm starting to get the hang of it I think.

This website CMS I'm working on is starting to get complicated and I wanted to add a new feature to the whole site. Firstly I should say that there is a free and a premium site to site because of the art that is being sold by artist (well at least that is what I'm going for), and I wanted to have a single page or panel that had the updates on it. Like new stock video, or new print, ect ect. So I made a table in sql but not I'm stuck on some logic problems with how that is updated because some days will not have some types of content versus other kinds, and I don't want to do it manually (who would with these tools in front of us), but rather it gets updated when I use the posting admin panel or an aritist dose.

Anyway, here is the real question. In php how do I check a table for a date from the database? Like an if() statement?

example of what I was thinking:(not code correct at all)

Code: Select all

<?
mysql_connect();
mysql_select_db();
//check if today has an entry
$sql = "SELECT * FROM table WHERE DATETIME = TODAY LIMIT 0,1";
$sql_result = mysql_query($sql);
if($sql_result){
//UPDATE ENTRY
}else{
//INSERT NEW ENTRY FOR TODAY INTO TABLE
}
?>
Not sure if that would work. Also I want to add a prepost to this. Like say I wanted to add some new stock art or something for sale, but I wanted it to post on the 1st of April, (and the system can account for this with just a DATETIME check versus current time in php if it should show on the site ;p), but not sure how I would check a datetime from php into mysql, if that makes since. I can get the post time from another table easily enough in the same page with doing this, in the format like "2011-01-04 08:53:00" and then I do know how to explode it and stuff ;p anyway, hope to hear from you guys.
User avatar
angelicodin
Forum Commoner
Posts: 81
Joined: Fri Nov 13, 2009 3:17 am
Location: Oregon, USA

Re: Checking MySQL for entry?

Post by angelicodin »

I did some more research on MySQL functions (I really should get a book I think, suggestions?) and this is what I came up with:

Code: Select all

<?php
//Example for a blog update Idea:
//NOTE: ASSUME ALREADY CONNECTED TO DB.

//check if there is a current entry for this day"
$sql = "SELECT * FROM update_table WHERE datetime_field BETWEEN '2011-03-22 00:00:00' AND '2011-03-22 23:59:59' LIMIT 0,1";
$result = mysql_query($sql);
if($result){
   //UPDATE CURRENT ENTRY
}else{
   //INSERT NEW ENTRY
}
?>
Would this work?
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Checking MySQL for entry?

Post by social_experiment »

Code: Select all

<?php
//Example for a blog update Idea:
//NOTE: ASSUME ALREADY CONNECTED TO DB.

//check if there is a current entry for this day"
$sql = "SELECT * FROM update_table WHERE datetime_field BETWEEN '2011-03-22 00:00:00' AND '2011-03-22 23:59:59' LIMIT 0,1";
$result = mysql_query($sql);
if($result){
   //UPDATE CURRENT ENTRY
}else{
   //INSERT NEW ENTRY
}
?>
Imo you would probably want to count the rows. Currently your code says : If the query ($result) is executed, update the current entry. No problem there, until the else happens because then there was a problem with the query i.e an error so your insert entry will not take place.

Code: Select all

<?php
 $sql = "SELECT COUNT(id) FROM update_table WHERE datetime_field BETWEEN '2011-03-22 00:00:00' AND '2011-03-22 23:59:59' ";
 $result = mysql_query($sql);
 $array = mysql_fetch_array($result);
 $rows = $array[0];

 // if there are more than 0 rows 
 if ($rows > 0) {
 // update
 }
 else {
 // add new 
 }
?>
Hth
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
angelicodin
Forum Commoner
Posts: 81
Joined: Fri Nov 13, 2009 3:17 am
Location: Oregon, USA

Re: Checking MySQL for entry?

Post by angelicodin »

social_experiment,

Yeah that makes total since to me. Also while I was waiting for a response I took a peek at the function mysql_num_rows(), would that be better to use, or pretty much the same as doing a MySQL query SELECT Count(id)?

edit: added code example of what I'm talking about here.

Code: Select all

<?php
$sql = "SELECT * FROM update_table WHERE datetime_field BETWEEN '2011-03-22 00:00:00' AND '2011-03-22 23:59:59' ";
$query = mysql_query($sql);
$numrows = mysql_num_rows($query);
if($numrows == 0){
   //INSERT NEW
}elseif($numrows > 0){
   //UPDATE
}else{
   //ERROR reporting
}
?>
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Checking MySQL for entry?

Post by social_experiment »

Either option will work, i just prefer using the COUNT() option more. If you do use the COUNT() function, try to use a column that has a value for each record, something like an auto-incrementing primary key.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
fugix
Forum Contributor
Posts: 207
Joined: Fri Mar 18, 2011 8:01 pm

Re: Checking MySQL for entry?

Post by fugix »

To add on to that, if you don't want to check the entire row for information you can search a specific column in a row by using the SELECT query command and sorting the columns into an array using mysql_fetch_array($query) or mysql_fetch_assoc($query) and selecting the specific column of information in a specific row
Post Reply