Data not inserting into database unless I refresh the page?

Question

I have the following files and this is what they do:

input.php: Grabs the data from XML feed, connects to and inputs it into the database.
output.php: Connects to the database and creates variables to be outputted.
index.php: Includes output.php and uses the variables to output the data.
refresh.js: Ajax to refresh the divs to update the data.

Input.php

$xml = simplexml_load_file( "http://domain.com/feed" );
$json = json_encode( $xml );
$array = json_decode( $json,TRUE );
$items = $array['channel']['item'];

$DB = new mysqli( 'localhost','USERNAME','PASSWORD','DATABASE' );
if( $DB->connect_errno ){
  print "failed to connect to DB: {$DB->connect_error}";
  exit( 1 );
}

$match = "#^(?:[^\?]*\?url=)(https?://)(?:m(?:obile)?\.)?(.*)$#ui";
$replace = '$1$2';

foreach( $items as $item ){
  $title = $item['title'];
  $url = preg_replace( $match,$replace,$item['link'] );
  $title_url[] = array( $title,$url );
  $sql_values[] = "('{$DB->real_escape_string( $title )}','{$DB->real_escape_string( $url )}')";
}
$SQL = "INSERT IGNORE INTO `read`(`title`,`url`) VALUES\n ".implode( "\n,",array_reverse( $sql_values ) );
if( $DB->query( $SQL ) ){
} else {
  print "failed to INSERT: [{$DB->errno}] {$DB->error}";
}
$DB->set_charset( 'utf8' );

Output.php

$DB = new mysqli( 'localhost','USERNAME','PASSWORD','DATABASE' );
if( $DB->connect_errno ){
  print "failed to connect to DB: {$DB->connect_error}";
  exit( 1 );
}

$query = "SELECT `title`,`url` FROM `read` ORDER BY `order` DESC";
$result = $DB->query( $query );
// error-handling: make sure query returned a result
if( $result ){
  while( $row = $result->fetch_array() ){
      // list gets values from a numeric array
      list( $title,$url ) = $row;
      // using [] (append) is much faster than using array_push()
      $data[] = array( 'title'=>$title,'url'=>$url );
}
$title = $data[0]['title'];
$url = $data[0]['url'];
}else{
  //do something
}

Index.php

<!doctype html>
<html>
<head>
  <meta charset="utf-8">
  <title>Recently</title>
  <script src="//ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
  <script src="js/refresh.js"></script>
</head>

<body>
  <?php include_once "includes/output.php"; ?>

  <p class="title"><span>Read </span>
    <a  id="read" href="<?php echo $url; ?>" target="_blank"><?php echo $title; ?></a>  
  </p>

</body>
</html>

Refresh.js

var auto_refresh = setInterval(
 function() {
   $('#read').load('index.php #read');
 }, 2000);

The problem is that unless I refresh input.php (which screws up the increment count), the data does not get inserted into the database. I have no idea why this is happening. Any thoughts?

Note: Because I am just starting to learn more about PHP and Databases, I'd prefer that my input.php and output.php code be minimally altered if need be.

Hopefully I explained my issue well. I'm willing to explain further if necessary. — Chris Burton, Jul 18 '13 at 18:44

score 2 · Accepted Answer · answered Jul 20 '13 at 11:37

2

Basically your current workflow is: execute input.php once => use JS to execute output.php in an interval to get the output. So you can see the problem is input is kinda static and output is dynamic.

You can modify your refresh script alone to: execute input.php => execute output.php => after some second execute input.php again => execute output.php again and so on:

var auto_refresh = setInterval(function() {
    $.get('input.php', function () { // call input here
        $('#read').load('index.php #read'); // call output here
    });    
}, 2000); // run again after 2 seconds, you might wanna make this number higher to offload i/o

Hope this helps.

answered Jul 20 '13 at 11:37

Hieu Nguyen

8,563
2
36
43

That makes a lot of sense. The only problem that I'm having now is that the increment value changes significantly on the next input into the database. See here: http://cloud.chrisburton.me/QMjh. The next input `order` value will be something like `1120` instead of `37`. – Chris Burton Jul 20 '13 at 11:47
Is that number important? Anyway you might insert one big value before so the current value is that big. You can always reset the auto increment value by using: `ALTER TABLE tablename AUTO_INCREMENT = 37` – Hieu Nguyen Jul 20 '13 at 11:51
It's not important, no. Just really annoying. It happens because of the AJAX. Using that SQL statement, I'd have to do that each time data is inserted into the database. – Chris Burton Jul 20 '13 at 11:54
No the `auto_increment` should be reset manually only once. After that it will increment by 1 each time there is an `INSERT` query, the way it works is: get the biggest value in that column and increment that number by 1. So of course you need to remove/modify the row with big value ID in order to have the low value next time. – Hieu Nguyen Jul 20 '13 at 11:59
Unfortunately that's not what it's doing. If I reset the increment to what the next insert should be, everything is fine. However, the following insert after that, the increment will be substantially higher. Therefore having to constantly edit the value. It doesn't do this if I remove the AJAX script. – Chris Burton Jul 20 '13 at 12:08
That's so weird. Please read more about auto increment here: http://stackoverflow.com/questions/4562479/how-does-mysql-auto-increment-work. Maybe you can try to insert one row manually and see how it goes. – Hieu Nguyen Jul 20 '13 at 12:19
I wouldn't suggest on using setInterval to automate functions on the off chance of a race condition with one response hasn't returned any data and two seconds have passed and then you initiate the second response, it could create some issues. – gmaliar Jul 20 '13 at 12:29
So what do you suggest? – Chris Burton Jul 20 '13 at 12:35
It depends on how fast the `input.php` is executed, looking at the code I doubt that it would take more than 0.5s to execute both, and @ChrisBurton always has an option to increase the interval time. Basically @Guy's solution would work too but if the input is fast to execute then that `refresh()` would be looped very fast too. – Hieu Nguyen Jul 20 '13 at 12:45

score 1 · Answer 2 · answered Jul 20 '13 at 08:55

1

Well it is because you called to input.php once.

Nowhere in your index.php you call it again so output.php is being called and it fetches the latest row ORDER BY order DESC and then $url = $data[0]['url'].

You have to call input.php again in order to have new data.

You can change your refresh.js to something like this:

(function refresh() {
  $.get('input.php', function() {
    $("#read").load("index.php", function() {
      refresh();
    });
  });
})(); // self-executing anonymous functions are better than setInterval() to handle ajax

answered Jul 20 '13 at 08:55

gmaliar

5,294
1
28
36

That seems to spit out extra content and substantially slow down the site. – Chris Burton Jul 20 '13 at 09:35
You need to somehow refresh the `input.php` if you'd like to get newer content, otherwise you'd have to iterate over the MySQL query ... i.e `index.php?i=3` and inside `output.php` you would have to `$index = $_GET['i'];` `$query = "SELECT title, url FROM read ORDER BY order DESC LIMIT $index, 1";` – gmaliar Jul 20 '13 at 10:56
So because I'm using `include_once`, it's causing issues? What if I remove `include_once` and just stick with `include`? – Chris Burton Jul 20 '13 at 11:12
Also, although this isn't incredibly important, this sort of thing kind of annoys me. How can I stop the AJAX from substantially increasing the increment value? – Chris Burton Jul 20 '13 at 11:17
@ChrisBurton, can you look at your database, I think your `INSERT IGNORE` isn't doing what you want it to do. – gmaliar Jul 20 '13 at 12:31
Hmm. Even if I use `INSERT INTO`, the value still jumps. – Chris Burton Jul 20 '13 at 12:34
Do you have duplicates? – gmaliar Jul 20 '13 at 12:35

score 0 · Answer 3 · answered Jul 18 '13 at 08:12

0

maybe your request is being cached. try to change:

   $('#read').load('index.php?r='+ Math.floor((Math.random()*100)+1) +' #read');

answered Jul 18 '13 at 08:12

trrrrrrm

11,362
25
85
130

Unfortunately this does not solve the issue. It still seems to occur =/. I literally have to refresh `input.php` for the Ajax to take effect. – Chris Burton Jul 18 '13 at 08:17

Oguzhan Ozel · Answer 4 · 2013-07-20T11:26:56.773

Merge your input.php and output.php in a single file (for example io.php).

// input.php content
// output.php content
// And instead of creating $title and $url variables use this

header('Content-type: application/json');
echo json_encode($data[0]);

No need to include any file in index.php anymore, just edit your refresh.js as shown below

setInterval(function(){ $.ajax({
  url: 'io.php',
  type: 'GET',
  dataType: 'json',
  cache: false,
  success: function(m){
    $('#read').text(m.title).attr('href',m.url);
  }
}); }, 2000);

And also no need to reload index.php. I think this is more efficient way

EDIT: Here's the complete code.

There may be another code blocks that requires including output.php in index.php or something that conflicts my approach. I don't know this kind of additional informations about your system. It's just a suggestion.

io.php

(input.php + output.php) I don't know that saving on database is still needed when these files are combined but you may use those saved data in another page.

$xml = simplexml_load_file( "http://domain.com/feed" );
$json = json_encode( $xml );
$array = json_decode( $json,TRUE );
$items = $array['channel']['item'];

$DB = new mysqli( 'localhost','USERNAME','PASSWORD','DATABASE' );
if( $DB->connect_errno ){
  print "failed to connect to DB: {$DB->connect_error}";
  exit( 1 );
}

$match = "#^(?:[^\?]*\?url=)(https?://)(?:m(?:obile)?\.)?(.*)$#ui";
$replace = '$1$2';

foreach( $items as $item ){
  $title = $item['title'];
  $url = preg_replace( $match,$replace,$item['link'] );
  $title_url[] = array( $title,$url );
  $sql_values[] = "('{$DB->real_escape_string( $title )}','{$DB->real_escape_string( $url )}')";
}
$SQL = "INSERT IGNORE INTO `read`(`title`,`url`) VALUES\n ".implode( "\n,",array_reverse( $sql_values ) );
if( $DB->query( $SQL ) ){
} else {
  print "failed to INSERT: [{$DB->errno}] {$DB->error}";
}
$DB->set_charset( 'utf8' );

$query = "SELECT `title`,`url` FROM `read` ORDER BY `order` DESC";
$result = $DB->query( $query );
// error-handling: make sure query returned a result
if( $result ){
  while( $row = $result->fetch_array() ){
      // list gets values from a numeric array
      list( $title,$url ) = $row;
      // using [] (append) is much faster than using array_push()
      $data[] = array( 'title'=>$title,'url'=>$url );
}

header('Content-type: application/json');
echo json_encode($data[0]);

}else{
  //do something
}

index.php

<!doctype html>
<html>
<head>
  <meta charset="utf-8">
  <title>Recently</title>
  <script src="//ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
  <script src="js/refresh.js"></script>
</head>

<body>

  <p class="title"><span>Read </span>
    <a  id="read" href="" target="_blank"></a>  
  </p>

</body>
</html>

refresh.js

setInterval(function(){ $.ajax({
  url: 'io.php',
  type: 'GET',
  dataType: 'json',
  cache: false,
  success: function(m){
    $('#read').text(m.title).attr('href',m.url);
  }
}); }, 2000);

I'm not following? Where am I supposed to put the `header()` and echo? — Chris Burton, Jul 20 '13 at 10:47
After you create io.php (or something else) by combining input.php and output.php put header() and echo instead of these lines `$title = $data[0]['title']; $url = $data[0]['url'];` — Oguzhan Ozel, Jul 20 '13 at 10:52
Is there any other way from what I already have? While the above code might be efficient, it's quite hard to understand. My knowledge of PHP and databases isn't all that great. The AJAX I have also includes another `.load()` for a completely different API. Was hoping to keep that. — Chris Burton, Jul 20 '13 at 11:03
I edited my answer so as to show all files. I hope this will be helpful for you. — Oguzhan Ozel, Jul 20 '13 at 11:27