PHP - Method For Removing Mysql Rows Older Than X Dates...

Hi,
Whenever it comes to dates I go blank and just hear/read "blah blah blah".  I'm sure there's a name for a condition like that but it's causing me a problem right now.

I am making a RSS feed aggregator, inserting RSS feed posts into a mysql db and want to be able to extract those posts in order by date. (so that I can create a new feed of the last posts of multiple rss feeds)

My problem is that the pubDate format for feeds is Fri, 29 Oct 2010 11:22:58 +0000 and that doesn't work with mysql.

Apart from having to reformat the date I have to take into consideration the +0000 part could be different depending on where in the world the feed was published.

Any pointers would be gratefully received!

Hi,

I'm trying to get the records that are between two dates to show on my screen, right now nothing comes up my code I'm using is below

Code: [Select]
$today = date('Y-m-d');
$lastweek = date('Y-m-d',strtotime('-7 days'));
$sql = "SELECT * from `memos` WHERE `date` BETWEEN '$today' AND '$lastweek' AND `delete` !='1'";
$result = mysql_query($sql);
echo "<ol>";
while ($row = mysql_fetch_array($result)) {
echo "<li>".$row['memo']."</li>" ;
}
echo "</ol>";

I am trying to create a simple calendar where a user can add/remove their availability date. This part works so far. Now what I am having trouble with is highlighting the already selected dates that are fetched from a MySQL database.

 

Here's my code.

// HTML
<div id="datetimepicker1"></div>

// Jquery/Ajax

<script>
$(document).ready(function () {

  $('#datetimepicker1').datepicker({
    dateFormat: "yy-mm-dd",
    multidate: true,
    onSelect: function () {
        var getDate = $("#datetimepicker1").val();

        $.ajax({
             type: "POST", //or GET. Whichever floats your boat.
             url: "snippets/adapter-set.php",
             data: { date: getDate },
             success: function(data) {
              // alert(data);
             },
             error: function() {
                 alert("Error.");
             }
        });
    }
  });
});
</script>

// PHP
// adapter-set.php

$post_date = $_POST['date'];

$find_query = $db->prepare("SELECT user_id FROM user_dates WHERE date_available = :date_available");
$find_query->bindParam(':date_available', $post_date);
$find_query->execute();
$result_find = $find_query->fetchAll(PDO::FETCH_ASSOC);
if(count($result_find) > 0) {
  foreach($result_find as $row) {
    $user_id =	$row['user_id'];
  }
  $delete_query = $db->prepare("DELETE FROM user_dates WHERE user_id = :user_id");
  $delete_query->bindParam(':user_id', $user_id);
  $delete_query->execute();
  $result_delete = $delete_query->execute();
  if($result_delete == false) {
   echo 'delete false';
  } else {
   echo 'delete success';
  }
} else {
  $insert_query = $db->prepare("INSERT INTO user_dates(date_available) VALUES(:date_available)");
  $insert_query->bindParam(':date_available', $post_date);
  $result_insert = $insert_query->execute();
  if($result_insert == false) {
   echo 'insert false';
  } else {
   echo 'insert success';
  }

}

 

The above code works fine. It inserts and deletes a date row in MySQL database table based on a click.

Now what I would like to do is to highlight all the "available" dates that are already inserted into the database; so that the user knows which dates he has already selected. This is my code for that. But it doesn't seem to be working. No errors. It's just not highlighting the inserted dates. Can you tell me what I'm doing wrong?

// JQUERY
<script>
    $(document).ready(function() {

        $.post('snippets/adapter-fetch.php', {}, function(data){
            $("#datetimepicker1").datepicker({
              datesEnabled : data.datesEnabled
            });
        }, 'json');

    });
</script>

// PHP
// adapter-fetch.php

$global_user_id = 5;

$find_query = $db->prepare("SELECT date_available FROM user_dates WHERE user_id = :user_id");
$find_query->bindParam(':user_id', $global_user_id);
$find_query->execute();
$result_find = $find_query->fetchAll(PDO::FETCH_ASSOC);
if(count($result_find) > 0) {
  foreach($result_find as $row) {
    $date_available =	$row['date_available'];

    echo $date_available;
  }
} else {
  echo 'not dates available';
}

 

I was fiddling around with php and concerning data in a mysql database.

All I was wanting to understand was being able to remove values with cheking multiple items in outputted html list.

One that reads a set of values from a database, then displays a check box allowing the user to delete such values, that's it basically, here is my script I used to do it:

Code: [Select]
<?php

function deleteRow($value)
{
  $sql = "DELETE FROM checkbox WHERE value LIKE '$value'";
  $result = mysql_query($sql);
}

if(array_key_exists('delete', $_POST)){

  if(isset($_POST['value']))
  {
    $count = count($_POST['value']);

$connect = mysql_connect('localhost', 'jeremy', 's56pj989');
if($connect)
{
  $select_db = mysql_select_db('test');
  if($select_db)
  {
        for($i=0; $i<$count; $i++){
  
          // print 'This is item = '.$_POST['value'][$i]; // outputs fine
      if(strlen($_POST['value'][$i]) > 0)
      {
            deleteRow($_POST['value'][$i]);
      }
    }
  }
}
  }
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
  "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en">

  <head>
    <title></title>
  </head>
  <body>
  <form id="myform" name="myform" action="<?=$_SERVER['PHP_SELF']; ?>" method="post">
<?php
if($_SERVER['REMOTE_ADDR'] == '192.168.0.2') {

  // printf("<p>Yes you have connected to this script successfully!</p>");
  
  $connect = mysql_connect('localhost', 'mydbuser', 'mydatabasepassword');
  
  if($connect) {
  
    // printf("<p>You have now connected to the database, well done!</p>");

$select_db = mysql_select_db('test');

if($select_db) {
  // printf("<p>Selected database now</p>");
  
  $sql = 'SELECT * FROM checkbox';
  
  $result = mysql_query($sql);
  if($result) {
    
if(mysql_num_rows($result)>0){
  while($row = mysql_fetch_assoc($result)) {
    printf("%s <input type=\"checkbox\" name=\"value[]\" value=\"%s\">\n<br />\n", $row['value'], $row['value']);
  }
} else {
  printf("<p>No rows in system!</p>");
}
  }
}
  }
}
?>
  <input type="submit" id="delete" name="delete" value="Delete" />
  </form>
  </body>
</html>
There must be a better way shouldn't there? I am not too concerned with error checking, ie if result of removing the values doesnt work, since I will just use this as an example of how to do this.

Going on from that, how would I allow it to update a set of say changed values, just purely out of interest, pretty happy since it works though this script.

But I was also wondering if there's some easy way of updating a set of values perhaps?

Hmm good though and I look forward to any replies,
Jeremy.

I would like to know what is the best method (the most intelligent and comfortable for users) to paginate a result table with PHP/MySQL?
Provided I have a result set of 100 pages.

Method 1:
<select>
<option>1</option>
....
</select>

Method 2:
Link on Page 1, 2, 3 ... 100

If there is any algorithm with PHP for automated these displays, please demonstrate it.

im wondering how to get php to read the latest row (instead of show them all) and when another one comes it replaces it once the page refreshes

How would I make a script that would delete any row in a MySQL table that was more than 7 days old?

Here's the error: Code: [Select]
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/stayway1/public_html/www.one-mafia.com/eng/inboxread.php on line 99
Here's lines 90-110: (99 in bold)

Code: [Select]
if (strip_tags($_POST['Accept_OC'])){
$oc_id=strip_tags($_POST['oc_id']);
if (strip_tags($_POST['place']) == "we"){
$use="we_inv";
$a="we";
$query= "SELECT * FROM oc WHERE we_inv='$username' AND id='$oc_id'";
}elseif (strip_tags($_POST['place']) == "ee"){
$use="ee_inv";
$a="ee";
$query= "SELECT * FROM oc WHERE ee_inv='$username' AND id='$oc_id'";

}elseif (strip_tags($_POST['place']) == "driver"){
$use="driver_inv";
$a="driver";
$query= "SELECT * FROM oc WHERE driver_inv='$username' AND id='$oc_id'";
}

$round=mysql_query($query);
[b]$check=mysql_num_rows($round);[/b]
if ($check != "0"){
echo "You are now in that OC";
mysql_query("UPDATE `oc` SET `$a`='$username' WHERE `id`='$oc_id'");
mysql_query("UPDATE `users` SET `oc`='1' WHERE `username`='$username'");
}
}
if (strip_tags($_POST['Yes_street'])){
$race_id=strip_tags($_POST['race_id']);
if ($fetch->street != "0"){ echo "Your in a race."; }elseif ($fetch->street == "0"){

if ($fetch->last_race >= time()){ echo "You cant do anouther race yet."; }elseif ($fetch->last_race < time()){

Now he sayes other fold:



Wrong parameter count for mysql_num_rows() in /home/a5078111/public_html/registerpage.html on line 146

I have a HTML dropdown list which is populated from a MySQL table. I use an auto-incremend ID field. I have also got a sort_order field.
 
I would like to be able to specify the exact order that entries should appear in the dropdown list. Specifically I need some functionality that does the following two things:
 
1) If I change the sort order of a record to for example "4" then the existing record with sort_order 4 becomes 5, sort_order 5 becomes 6 and so on.
 
2) If I change the sort order of a record to for example "4" and there is currently no record with sort_order 4, then no other records will be updated.

Ok I am trying to delete some rows from a database. I have done this before with success so I thought I could use the same method just modify it a little and it would work. Man was I wrong. Any help on this is much appreciated below is my code.

Code for the Query to get the data from the database and for displaying the results in a table:
Code: [Select]

//GET DATA
$sql = "SELECT * FROM `".$tblname."` ORDER BY $orderby $sort LIMIT $startrow,$limit";
$result = mysql_query($sql) or die(mysql_error());
$result2 = mysql_query($sql);

//START TABLE AND TABLE HEADER
echo "<form name='form1' method='post' action=''><table style='font-size:9.5px;'>\n<tr><th>Delete</th>";
$array = mysql_fetch_assoc($result);
foreach ($array as $key=>$value) {
if($config['nicefields']){
$field = str_replace("_"," ",$key);
$field = ucwords($field);
}

$field = columnSortArrows($key,$field,$orderby,$sort);
echo "<th>" . $field . "</th>\n";
}
echo "</tr>\n";

//reset result pointer
mysql_data_seek($result,0);

//start first row style
$tr_class = "class='odd'";

//LOOP TABLE ROWS
while($row = mysql_fetch_assoc($result)){


echo "<tr ".$tr_class.">\n<td><a
 href='remove_rec.php?id=". $rows['ID'] . "'><p>Delete</p></a>";
echo "</td>";
foreach ($row as $field=>$value) {
echo "<td>" . $value . "</td>\n";
}
echo "</tr>\n";
//switch row style
if($tr_class == "class='odd'"){
$tr_class = "class='even'";
}else{
$tr_class = "class='odd'";
}

}

//END TABLE
echo "</table>\n</form>";

Here is the code that should delete the row:
Code: [Select]
$tblname = 'tablename';
// get value of id that sent from address bar
$id=$_GET['ID'];

// Delete data in mysql from row that has this id
$sql="DELETE FROM $tblname WHERE ID='$id'";
$result=mysql_query($sql);

// if successfully deleted
if($result){
echo "Deleted Successfully";
echo "<BR>";
echo "<a href='leads3.php'>Back to Results</a>";
}

else {
echo "ERROR";
}

When I click on the link to delete a record it redirects me to the appropriate page with the "Deleted Successfully" message but when I go to view the results the row was not deleted. Any help on this would, again, be greatly appreciated.

I'm trying to pull user referral data from my database. This is what I have so far.


<table cellpadding="0" cellspacing="0" style="border:1px #000000 solid;" width="68%">
<tr>
<td bgcolor="#eeeeee" style="padding:2px;border-right:1px #000 solid;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b>
User
</b></font></td>
<td bgcolor="#eeeeee" style="padding:2px;border-right:1px #000 solid;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b>
Email
</b></font></td>
<td bgcolor="#eeeeee" style="padding:2px;border-right:1px #000 solid;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b>
Date
</b></font></td>
<td bgcolor="#eeeeee" style="padding:2px;border-bottom:1px #000 solid;"><font size="2" face="verdana"><b>
Visits
</b></font></td>
</tr>


<?

$lole=$_COOKIE["usNick"];

$tabla = mysql_query("SELECT * FROM tb_users where username='$lole' ORDER BY id ASC"); 

while ($row = mysql_fetch_array($tabla)) { 




echo "<tr><td><font size=\"2\" face=\"verdana\">";

echo $row["username"];

echo "</font></td><td><font size=\"2\" face=\"verdana\">";

echo $row["email"];

echo "</font></td><td><font size=\"2\" face=\"verdana\">";

echo $row["joindate"];

echo "</font></td><td><font size=\"2\" face=\"verdana\">";

echo $row["visits"];

echo "</font></td></tr>";

}

echo "</table>";

?>




<script type="text/javascript">
initSortTable('myTable',Array('S','N','S','N','S'));
</script>



I just found out my user ID is missing from database..

I have a MySQL query that returns rows containing date_add column like below:

DATE(date_add)
2014-01-07
2014-01-07
2014-01-07
2014-01-07
2014-01-08
2014-01-15
2014-01-20
2014-01-20
2014-01-20
2014-01-26
2014-01-28
2014-02-16
2014-02-16
2014-02-16
2014-02-16
2014-02-16
2014-02-16
2014-02-16
2014-02-18
2014-02-18
2014-03-08
2014-03-08

How can I have pagination based on dates and show rows with the same date in a page and not just based on predefined limit number?

First off I know this will be a problem with my code but I honestly have looked at it for too long. If anyone can shed a bit of simple light on it that would be great.

$count should return about 8 or 9 or so but just returns 1 - I can only print out the number 1 if I do a direct echo of the mysql_num_rows() instead of putting it into the $count variable. Its really confusing, the database is all set up correctly as earlier on in the page I get all the information from the database and print out a table with it. There is something wrong here!

Problem only occurs when $count = mysql_num_rows($result1); is written.

Code: [Select]
//Following user presses button with name applyChanges.

if(isset($_POST['applyChanges']))
{
$result1 = mysql_query("SELECT * FROM music") or die ("Could not get result1");
$count = mysql_num_rows($result1) or die ("Could not count rows"); //Changed this to an echo without $count and prints a 1. - Should be greater than 8
echo "$count";

for($i=0; $i<$count; $i++)
{
$query2 = "UPDATE music SET name='$name[$i]' WHERE path='$path[$i]'";
$result2 = mysql_query($query2);
}
}
Thanks,
Matt

Hello,

I've been following google maps with php/mysql turorial http://code.google.com/apis/maps/articles/phpsqlajax.html

I've come to my phpsqlajax_genxml.php file which as the google code has it like this:

<?php
require("phpsqlajax_dbinfo.php");

// Start XML file, create parent node
$doc = domxml_new_doc("1.0");
$node = $doc->create_element("markers");
$parnode = $doc->append_child($node);

// Opens a connection to a MySQL server
$connection=mysql_connect (localhost, $username, $password);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
}

// Select all the rows in the markers table
$query = "SELECT * FROM markers WHERE 1";
$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}

header("Content-type: text/xml");

// Iterate through the rows, adding XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
  // ADD TO XML DOCUMENT NODE
  $node = $doc->create_element("marker");
  $newnode = $parnode->append_child($node);

  $newnode->set_attribute("name", $row['name']);
  $newnode->set_attribute("address", $row['address']);
  $newnode->set_attribute("lat", $row['lat']);
  $newnode->set_attribute("lng", $row['lng']);
  $newnode->set_attribute("type", $row['type']);
}

$xmlfile = $doc->dump_mem();
echo $xmlfile;

?>


I started with this but was getting errors at line 1!! I looked around abit on the net and found i should change

$doc = domxml_new_doc("1.0");

to this

$doc = new DOMDocument('1.0');

Then i got errors abour creating the elements, i looke on php.net and it looked like i needed to change

$node = $doc->create_element("markers");
$parnode = $doc->append_child($node);
$newnode->set_attribute("name", $row['name']);


To this:

$node = $doc->createElement('markers');
$parnode = $doc->appendChild($node);
$newnode->setAttribute ('name', $row['name']);

This at least got my code further down the page until the very last statment:

$xmlfile = $doc->dump_mem();

Here i'm getting this error "Fatal error: Call to undefined method DOMDocument::dump_mem()" along with "Warning: Cannot modify header information - headers already sent"

I havent even looked at the warning i just tried to fix the fatal error. Again i looked around the web and forums and php.net and thought maybe i need to set the value to "true" but it still got a fatal error.

I looked on php.net and in there examples for dump_mem() and they had the nodes set_attribute create_element and append_child.

Which confused me as that was what i used originally just following the google script and that got me the error right away "Fatal error to undefined method DOMDocument::create"


Can anybody point out whats going wrong? This is my script that I am using

<?php
require('connect.php');
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
<title>phpsqlajax_genxml.php</title>
</head>

<body>
<?php
// Start XML file, create parent node
$doc = new DOMDocument('1.0');
$node = $doc->createElement('markers');
$parnode = $doc->appendChild($node);

// Select all rows in markers table
$query = "SELECT * FROM markers WHERE 1";
$result = mysql_query($query);
if (!$result) {
die ('Invalid query: ' . mysql_error());
}
header('Content-type: text/xml');

// Iterate through the rows, addind xml nodes for each
while ($row = mysql_fetch_assoc($result)) {
// Add to XML document node

$node = $doc->createElement('marker');
$newnode = $parnode->appendChild($node);

$newnode->setAttribute ('name', $row['name']);
$newnode->setAttribute ('address', $row['address']);
$newnode->setAttribute ('lat', $row['lat']);
$newnode->setAttribute ('lng', $row['lng']);
$newnode->setAttribute ('type', $row['type']);
}

$xmlfile = $doc->dump_mem();
echo $xmlfile;
?>

</body>

</html>


And the google code is on the link in posted in the messge also but this is theres both of which fail for Fatal errors either on the dump_mem() or new DOMDocument respecitvaley :

<?php
require("phpsqlajax_dbinfo.php");

// Start XML file, create parent node
$doc = domxml_new_doc("1.0");
$node = $doc->create_element("markers");
$parnode = $doc->append_child($node);

// Opens a connection to a MySQL server
$connection=mysql_connect (localhost, $username, $password);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
}

// Select all the rows in the markers table
$query = "SELECT * FROM markers WHERE 1";
$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}

header("Content-type: text/xml");

// Iterate through the rows, adding XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
  // ADD TO XML DOCUMENT NODE
  $node = $doc->create_element("marker");
  $newnode = $parnode->append_child($node);

  $newnode->set_attribute("name", $row['name']);
  $newnode->set_attribute("address", $row['address']);
  $newnode->set_attribute("lat", $row['lat']);
  $newnode->set_attribute("lng", $row['lng']);
  $newnode->set_attribute("type", $row['type']);
}

$xmlfile = $doc->dump_mem();
echo $xmlfile;

?>



Thanks to anybody who can help me out