How to use cURL to fetch specific data from a website and then save it my database using php

Question 1

can any one tell me how to use curl or file_get_contents for downloading specific data from a website and then save those specific data into my mysql database. I want to get latest additions of films from this website http://www.traileraddict.com/ and i want to save it in my database(on a daily basis; this text and html link will be shown on my website). I just need the text and html link.(highlighted in the pic)

i have searched everywhere but i didnt find any useful tutorial. i have two main questions to ask

1) How can i get specific data using cURL or file_get_contents.

2) How can i save the specific content to my mysql database table( text in one column and link in another column)

Question 2

Using cURL:

$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, 'http://www.something.com');
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, true);

$content = curl_exec($ch);

Then you can load the element into a DOM Object and parse the dom for the specific data. You could also try and parse the data using search strings, but using regex on HTML is highly frowned upon.

$dom = new DOMDocument();
$dom->loadHTML( $content );

// Parse the dom for your desired content

http://www.php.net/manual/en/class.domdocument.php

Question 3

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
}
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

Question 4

I see at least 2 errors in your code.

the $column_names associative array values are supposed to be passed as field names, so I assume that they are not correct, as you have spaces in them (and as I know wordpress by default does not have such field names.
if some selection is provided by user you are adding some extra field names to the once which are passed by user and you have a colon after them so it will generate an error.

I would rewrite the code like this

<?php
$all = false;
$column_names = array('1' => '`field1`', '2' => '`field2`', '3' => '`field3`');
if(isset($_POST['columns'])){
    $column_entries = $_POST['columns'];
    $sql_columns = array();
    foreach($column_entries as $i) {
        if(array_key_exists($i, $column_names)) {
            $sql_columns[] = $column_names[$i];
        }
    }
    $sql_columns[] = "authorss";
    $sql_columns[] = "research_source";
    $sql_columns[] = "research_title";
} else {
    $all = true;
    $sql_columns[] = "*";
}

Also as you have said $wpdb->get_results returns already the results - array so that's why you get the errors. Plus before calling mysql_fetch_assoc it is better to check if the passed parameter is recource and if the number of rows is not 0.

if($result!==false && mysql_num_rows($result)>0){
    while( $row = mysql_fetch_assoc($result)){
        ...
    }
}

*********** UPDATE ***********

according to last changes try this code:

<?php
$all = false;
$column_names = array('1' => '`authorss`', '2' => '`research_source`', '3' => '`research_title`');
if(isset($_POST['columns'])){
    $column_entries = $_POST['columns'];
    $sql_columns = array();
    foreach($column_entries as $i) {
        if(array_key_exists($i, $column_names)) {
            $sql_columns[] = $column_names[$i];
        }
    }
} else {
    $all = true;
    $sql_columns[] = "authorss";
    $sql_columns[] = "research_source";
    $sql_columns[] = "research_title";
}

global $wpdb;

//DNI CHECKBOX + ALL
$tmp = $wpdb->get_results( "SELECT ".implode(",", $sql_columns)." FROM wp_participants_database"); 


echo "<table border='1' style='width:450px'>
    <tr>
    <th>authorss</th>
    <th>research_source</th>
    <th>research_title</th>";
foreach($column_names as $k => $v) { 
    if($all || (is_array($column_entries) && in_array($k, $column_entries)))
        echo "<th>$v</th>";
}
echo "</tr>";

if(count($tmp)>0){
    for($i=0;$i<count($tmp);$i++){
        echo "<tr>";  
            foreach($tmp[$i] as $key=>$value){
                echo "<td>" . $value . "</td>";   
            }
            foreach($column_names as $k => $v) { 
                if($all || (is_array($column_entries) && in_array($k, $column_entries))) {
                    echo "<th>".$row[$v]."</th>";
                }
            }
        echo "</tr>";
    }
}

echo '</table>';
?>

Question 5

From forosdelweb

We are going to create a class to make the convertion in convertToPDF.php

<?php 
/*----------------------------------------------------------/* 

$path     : name of the file pdf (without the extension) 
                i.e.: --> 'ejemplo' , 'pdfs/nuevo-ejemplo' 
                if empty --> it will be random 

$content  : content of the pdf 

$body     : true or false. 
                true  --> add; <doctype>, <body>, <head> to $content 
                false --> do not alter the $content 

$style    : the path of the CSS. It could be empty 
                 To load the css --> needs $body = true; 

$mode     : true or false. 
                true  --> save the file on the server and then show it  
                false --> ask where to save it  

$paper_1  : size of the paper
 
$paper_2  : style of the paper
 

    
 To see more options:  
        --> http://code.google.com/p/dompdf/wiki/Usage#Invoking_dompdf_via_the_command_line 

/*----------------------------------------------------------*/  

require_once("dompdf/dompdf_config.inc.php"); 

function doPDF($path='',$content='',$body=false,$style='',$mode=false,$paper_1='a4',$paper_2='portrait') 
{     
    if( $body!=true and $body!=false ) $body=false; 
    if( $mode!=true and $mode!=false ) $mode=false; 

    if( $body == true ) 
    { 
        $content=' 
        <!doctype html> 
        <html> 
        <head> 
            <link rel="stylesheet" href="'.$style.'" type="text/css" /> 
        </head> 
        <body>' 
            .$content. 
        '</body> 
        </html>'; 
    } 

    if( $content!='' ) 
    {         
        //Añadimos la extensión del archivo. Si está vacío el nombre lo creamos 
        $path!='' ? $path .='.pdf' : $path = crearNombre(10);   

        //Las opciones del papel del PDF. Si no existen se asignan las siguientes:[*] 
        if( $paper_1=='' ) $paper_1='a4'; 
        if( $paper_2=='' ) $paper_2='portrait'; 

        $dompdf =  new DOMPDF(); 
        $dompdf -> set_paper($paper_1,$paper_2); 
        $dompdf -> load_html(utf8_encode($content)); 
        //ini_set("memory_limit","32M"); //opcional  
        $dompdf -> render(); 

        //Creamos el pdf 
        if($mode==false) 
            $dompdf->stream($path); 

        //Lo guardamos en un directorio y lo mostramos 
        if($mode==true) 
            if( file_put_contents($path, $dompdf->output()) ) header('Location: '.$path); 
    } 
} 

function crearNombre($length) 
{ 
    if( ! isset($length) or ! is_numeric($length) ) $length=6; 

    $str  = "0123456789abcdefghijklmnopqrstuvwxyz"; 
    $path = ''; 

    for($i=1 ; $i<$length ; $i++) 
      $path .= $str{rand(0,strlen($str)-1)}; 

    return $path.'_'.date("d-m-Y_H-i-s").'.pdf';     
}

The next step is to create a page in this case index.php

<?php 

include('convertToPDF.php'); 

//$html= --> Aquí pondriamos por ejemplo la consulta 
$html=' 
<img src="http://pxd.me/dompdf/www/images/title.gif"/> 

<table> 
    <tr> 
        <th>Nombre</th> 
        <th>Tipo</th> 
        <th>Imagen</th> 
        <th>Comentario</th> 
        <th>Unidades</th> 
        <th>Precio unidad</th>     
    </tr>     
    <tr> 
        <td>pensandoo</td> 
        <td>icono</td> 
        <td><img src="http://static.forosdelweb.com/fdwtheme/images/smilies/scratchchin.gif"/></td> 
        <td>iconito pensativo</td> 
        <td>3</td> 
        <td>10</td> 
    </tr> 
    <tr> 
        <td>fiesta</td> 
        <td>icono 3</td> 
        <td><img src="http://static.forosdelweb.com/fdwtheme/images/smilies/porra.gif"/></td> 
        <td>iconito festejando</td> 
        <td>1</td> 
        <td>24</td> 
    </tr> 
    <tr> 
        <td>silbando</td> 
        <td>icono</td> 
        <td><img src="http://static.forosdelweb.com/fdwtheme/images/smilies/silbar.gif"/></td> 
        <td>bombilla silbando</td> 
        <td>19</td> 
        <td>50</td> 
    </tr> 
    <tr> 
        <td>no no no</td> 
        <td>icono 2</td> 
        <td><img src="http://static.forosdelweb.com/fdwtheme/images/smilies/negar.gif"/></td> 
        <td>negacion</td> 
        <td>5</td> 
        <td>1</td> 
    </tr> 
</table> 
' 

?> 

<?php 

if ( isset($_POST['PDF_1']) ) 
    doPDF('ejemplo',$html,false); 

if ( isset($_POST['PDF_2']) ) 
    doPDF('ejemplo',$html,true,'style.css'); 

if ( isset($_POST['PDF_3']) ) 
    doPDF('',$html,true,'style.css'); 

if ( isset($_POST['PDF_4']) ) 
    doPDF('ejemplo',$html,true,'style.css',false,'letter','landscape');  

if ( isset($_POST['PDF_5']) ) 
    doPDF('ejemplo',$html,true,'',true); //asignamos los tags <html><head>... pero no tiene css 

if ( isset($_POST['PDF_6']) ) 
    doPDF('',$html,true,'style.css',true); 

if ( isset($_POST['PDF_7']) ) 
    doPDF('pdfs/nuevo-ejemplo',$html,true,'style.css',true); //lo guardamos en la carpeta pdfs     
?> 

<!doctype html> 
<html> 

<head> 
    <link rel="stylesheet" href="style.css" type="text/css" /> 
</head> 

<table class="header"> 
    <tr> 
        <td><a href="http://www.forosdelweb.com/f18/" target="_blank"><h1>PHP</h1></a></td> 
        <td><a href="http://www.forosdelweb.com/" target="_blank"><h2>FOROSDELWEB</h2></a></td> 
    </tr> 
</table> 

<table class="menu"> 
    <tr> 
        <td>Ejemplos para: <strong>dompdf</strong></td> 
        <td><a href="http://code.google.com/p/dompdf/wiki/Usage" target="_blank">Documentaci&oacute;n</a></td> 
        <td><a href="http://code.google.com/p/dompdf/source/browse/trunk/dompdf/dompdf_config.custom.inc.php?r=399" target="_blank">Define()</a></td> 
        <td><a href="http://pxd.me/dompdf/www/examples.php#samples" target="_blank">Ejemplos de dompdf</a></td> 
    </tr> 
</table> 

<body> 

<?php echo $html ?> 

<form  action="<?php echo $_SERVER['PHP_SELF'] ?>" method="POST"> 
<table> 
  <tr> 
    <td>Mostrar PDF sin CSS</td> 
    <td><input name="PDF_1" type="submit" value="CREAR" /></td> 
  </tr> 
  <tr> 
    <td>Mostrar PDF con CSS</td> 
    <td><input name="PDF_2" type="submit" value="CREAR" /></td> 
  </tr> 
  <tr> 
    <td>Mostrar PDF con CSS sin definir el nombre</td> 
    <td><input name="PDF_3" type="submit" value="CREAR" /></td> 
  </tr> 
  <tr> 
    <td>Mostrar PDF con CSS y cambiando el formato de la hoja</td> 
    <td><input name="PDF_4" type="submit" value="CREAR" /></td> 
  </tr> 
  <tr> 
    <td>Guardar y abrir PDF sin CSS</td> 
    <td><input name="PDF_5" type="submit" value="CREAR" /></td> 
  </tr> 
  <tr> 
    <td>Guardar y abrir PDF con CSS sin definir el nombre</td> 
    <td><input name="PDF_6" type="submit" value="CREAR" /></td> 
  </tr> 
  <tr> 
    <td>Guardar en otro directorio y abrir PDF con CSS</td> 
    <td><input name="PDF_7" type="submit" value="CREAR" /></td> 
  </tr>   

</table> 

</form> 

</body> 
</html>

Finally you can have the file for your style style.css

body{
font:12px Arial, Tahoma, Verdana, Helvetica, sans-serif;
background-color:#BECEDC;
color:#000;
}

a h1{
font-size:35px; 
color:#FFF;
}

h2{
color:#FC0;
font-size:15px; 
}

table{
width:100%;
height:auto;
margin:10px 0 10px 0;
border-collapse:collapse;
text-align:center;
background-color:#365985;
color:#FFF;
}

table td,th{
border:1px solid black;
}

table th{
color:#FC0; 
}

.menu{
background-color:#69C;
color:#FFF;
}

.menu a{
color:#FFF; 
}

Question 6

The function you're looking for is find_in_set:

 select * from ... where find_in_set($word, pets)

for multi-word queries you'll need to test each word and AND (or OR) the tests:

  where find_in_set($word1, pets) AND find_in_set($word2, pets) etc

How to use cURL to fetch specific data from a website and then save it my database using php

Answers

php,mysql