Mysql to mysqli error

[gmc1103]

Hi

I'm trying to update my queries to mysqli but i'm having a lot of problems and i can't get them to work using this.

 

This is my mysql usage

$conexion = mysql_connect("localhost", "xxxxx", "xxxxx");
mysql_select_db("xxxxx", $conexion);
$queEmp = "SELECT t1.`id_plano` , t2.`cargo` , t1.`atividade` , t1.`descricao` , t1.`obj` , t1.`periodo` , t1.`previsao` , t1.`data_prevista` , t1.`orcamento` , t1.`destinatarios`, t1.`observacoes` 
FROM  `new_pae` AS t1 INNER JOIN  `pae_cargo` AS t2 ON ( t1.`id_cargo` = t2.`idcargo` ) where t1.`periodo` = '$search' ORDER BY  `data_prevista` ASC" ;
mysql_query ("set character_set_client='utf8'");
$resEmp = mysql_query($queEmp, $conexion) or die(mysql_error());
$totEmp = mysql_num_rows($resEmp);
if($totEmp<=0){
    $sem = utf8_decode("<b>Não existem registos</b>\n");
    $pdf->setColor(1, 0, 0);
    $pdf->addText(210, 669, 12, $sem);    
}
$ixx = 0;
while($datatmp = mysql_fetch_assoc($resEmp)) { 
	$ixx = $ixx+1;
	$data_chegada[] = array_merge($datatmp, array('num'=>$ixx));
}

The main problem is when i'm trying to put everything in the array and then use array_merge..

 

Anyone can point me to the right solution?

 

Thanks

[benanamen]

I'm trying to update my queries to mysqli 

 

There is nothing Mysqli about your code.

Edited November 2, 2015 by benanamen

[gmc1103]

I told i want to update to mysqli

 

This is mysqli

$mysqli = new mysqli('localhost', 'xxxx', 'xxxxx', 'xxxxxx');
if (mysqli_connect_errno()) {
    trigger_error('Database connection failed: ' . mysqli_connect_error(), E_USER_ERROR);
}
mysqli_set_charset($mysqli,"utf8");
$sql = "SELECT t1.`id_plano` , t2.`cargo` , t1.`atividade` , t1.`descricao` , t1.`obj` , t1.`periodo` , t1.`previsao` , t1.`data_prevista` , t1.`orcamento` , t1.`destinatarios`, t1.`observacoes` 
FROM  `new_pae` AS t1 INNER JOIN  `pae_cargo` AS t2 ON ( t1.`id_cargo` = t2.`idcargo` ) where t1.`periodo` = ? ORDER BY  `data_prevista` ASC ";
$stmt = $mysqli->prepare($sql);
if ($stmt === false) {
    trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $mysqli->error, E_USER_ERROR);
}
$stmt->bind_param('s', $search);
$stmt->execute();
$stmt->bind_result($id_plano, $cargo, $atividade, $descricao, $obj, $periodo, $previsao, $data, $orcamento, $destinarios, $observacoes);
$ixx = 0;
while ($datatmp = $stmt ->fetch()) {
    $ixx = $ixx+1;
    

$data_chegada[] = array_merge($stmt, array('num'=>$ixx));

} 

and the error is:

 

Warning: array_merge(): Argument #1 is not an array i

 

this line

$data_chegada[] = array_merge($stmt, array('num'=>$ixx));

any help?

[benanamen]

You cant use $stmt

 

Try this

 

$data_chegada[] = array_merge($datatmp, array('num'=>$ixx)); 

[gmc1103]

Hi

 

Thanks for your help

 

The same error

Warning: array_merge(): Argument #1 is not an array in 

while ($datatmp = $stmt ->fetch()) {
    $ixx = $ixx+1;
    $data_chegada[] = array_merge($datatmp, array('num'=>$ixx));
}

[mac_gyver]

with a mysqli prepared query, $stmt ->fetch() does NOT return the row, it causes the bound result variables to be populated with the data that is fetched.

 

a) it is much easier to use prepared queries with PDO, not mysqli.

 

b) if your php version is high enough, use the ->get_result() method to convert the result from the mysqli prepared query into a normal mysqli result resource, that you can use a normal ->fetch_assoc() method on. this will also allow you to use the ->fetch_all() method to fetch all the rows into an array.

Edited November 2, 2015 by mac_gyver

[gmc1103]

Hi mac_gyver

 

Thank you, so if i undertstood what you told

 

I used

 

stmt->bind_param('s', $search);
$stmt->execute();
$result = $stmt->get_result();
$stmt->bind_result($id_plano, $cargo, $atividade, $descricao, $obj, $periodo, $previsao, $data, $orcamento, $destinarios, $observacoes);
while ($row = $result->fetch_array(MYSQLI_NUM))
{
    $ixx = 0;
    foreach ($row as $r)
    {
        $ixx = $ixx+1;
        $data_chegada[] = array_merge($r, array('num'=>$ixx));
    }
}

and now i have

Fatal error: Call to undefined method mysqli_stmt::get_result()

Edited November 2, 2015 by gmc1103

[gmc1103]

I used pdo like suggested but i'm stuck again

try
{
    $DB_con = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
    $DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
    echo $e->getMessage();
}
$query = $DB_con->query("SELECT t1.id_plano , t2.cargo , t1.atividade , t1.descricao , t1.obj , t1.periodo , t1.previsao , t1.data_prevista , t1.orcamento , t1.destinatarios, t1.observacoes 
FROM  new_pae AS t1 INNER JOIN pae_cargo AS t2 ON (t1.id_cargo=t2.idcargo) t1.periodo =:me ORDER BY data_prevista ASC");
$query->$DB_con->prepare($sql);
if (!$query) {
        echo "\nPDO::errorInfo():\n";
        print_r($DB_con->errorInfo());
    }
$query->bindparam(":me", $search,PDO::PARAM_STR);
$fetch = $query->fetchAll();
$query->bind_result($id_plano, $cargo, $atividade, $descricao, $obj, $periodo, $previsao, $data, $orcamento, $destinarios, $observacoes);
$ixx = 0;
foreach ($fetch as $plano) {
    $ixx = $ixx+1;
    $data_chegada[] = array_merge($plano, array('num'=>$ixx));	
}

Now the error is 

 

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1.periodo =:me ORDER BY data_prevista ASC' at line 2'

 

So my sql syntax have error but i can't see anyone

[Barand]

You are missing the word "WHERE" in the query

[gmc1103]

Hi Barand

 

Thank you

 

Made a few change now no error but no data too

$query = 'SELECT t1.id_plano , t2.cargo , t1.atividade , t1.descricao , t1.obj , t1.periodo , t1.previsao , t1.data_prevista , t1.orcamento , t1.destinatarios, t1.observacoes 
FROM  new_pae AS t1 INNER JOIN pae_cargo AS t2 ON (t1.id_cargo=t2.idcargo) where t1.periodo = :me ORDER BY data_prevista ASC';
$stmt = $DB_con->prepare($query);
if (!$stmt) {
        echo "\nPDO::errorInfo():\n";
        print_r($DB_con->errorInfo());
    }
$stmt->bindparam(":me", $search,PDO::PARAM_STR);
$stmt->execute();
$ixx = 0;
while($result = $stmt->fetchAll(PDO::FETCH_ASSOC)){
    $ixx = $ixx+1;
    $data_chegada[] = array_merge($result, array('num'=>$ixx));
}

Since this is a pdf, how can i check if the $data_chegada array is not empty?