Jump to content

Mysql to mysqli error


gmc1103

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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 by gmc1103
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.