gmc1103 Posted November 2, 2015 Share Posted November 2, 2015 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 Quote Link to comment Share on other sites More sharing options...
benanamen Posted November 2, 2015 Share Posted November 2, 2015 (edited) I'm trying to update my queries to mysqli There is nothing Mysqli about your code. Edited November 2, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
gmc1103 Posted November 2, 2015 Author Share Posted November 2, 2015 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? Quote Link to comment Share on other sites More sharing options...
benanamen Posted November 2, 2015 Share Posted November 2, 2015 You cant use $stmt Try this $data_chegada[] = array_merge($datatmp, array('num'=>$ixx)); Quote Link to comment Share on other sites More sharing options...
gmc1103 Posted November 2, 2015 Author Share Posted November 2, 2015 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)); } Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted November 2, 2015 Share Posted November 2, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
gmc1103 Posted November 2, 2015 Author Share Posted November 2, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
gmc1103 Posted November 2, 2015 Author Share Posted November 2, 2015 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 2, 2015 Share Posted November 2, 2015 You are missing the word "WHERE" in the query Quote Link to comment Share on other sites More sharing options...
gmc1103 Posted November 2, 2015 Author Share Posted November 2, 2015 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.