Transform result into array and take all values in "select"

Asked

Viewed 1,437 times

2

I’m having a problem when creating a system, I need to make a select:

$select = $mysqli->query("SELECT * FROM `guild` WHERE `user_id`='$userid'");
$row = $select->num_rows;
$get = $select->fetch_array();
$guild = $get['guild'];

Transform the variable $guild in one array and catch it in the other select

$select = $mysqli->query("SELECT * FROM `guild` WHERE `guild` != '$guild' AND `user_id` != '$userid' ORDER BY `score` DESC LIMIT 5");
$row = $select->num_rows;
$get = $select->fetch_array();
$guild_name = $get['guild'];
$guild_icn = $get['icn'];
$guild_score = $get['score'];

Exactly what I need is that in this select all results of the array, but I have no idea how to do that.

Complete code: 

<?php
require_once ("includes/connection.php");
require_once ("includes/start-session.php");
require_once ("includes/encript.php");
$ip = $_SERVER["REMOTE_ADDR"];
$iusername = "Trabzera";
$userid = "10";
$select = $mysqli->query("SELECT * FROM `data` WHERE username='$iusername' AND `ip`='$ip'");
$row = $select->num_rows;
$get = $select->fetch_array();
if ($row > 0) {
$select = $mysqli->query("SELECT * FROM `guild` WHERE `user_id`='$userid'");
$row = $select->num_rows;
$get = $select->fetch_array();
$guild = $get['guild'];
#----------------------
$select = $mysqli->query("SELECT * FROM `guild` WHERE `guild` != '$guild' AND `user_id` != '$userid' ORDER BY `score` DESC LIMIT 5");
$row = $select->num_rows;
$get = $select->fetch_array();
$guild_name = $get['guild'];
$guild_icn = $get['icn'];
$guild_score = $get['score'];
$score_result = round($guild_score/1000);
$count = 1;
echo '<div id="guild-central">Central da Guild</div>
<div id="guild-central-msg">Selecione abaixo a opção desejada:</div>
<menu id="create-guild">
<div id="create-guild-msg">Criar uma Guild</div>
<div id="create-guild-txt">Para criar uma Guild é necessário alguns requerimentos:</div>
<div id="create-guild-req1">- Nível 10+</div>
<div id="create-guild-req2">- 1000 KP (Knautiluz Points)</div>
<div id="create-guild-reqok">Caso atenda esses requsistos clique abaixo:</div>
<button id="create-guild-button">Criar uma Guild</button>
</menu>';
echo '<menu id="join-guild">
<div id="join-guild-msg">Juntar-se a uma Guild</div>';
#-----------------------------------------------------
if ($row > 0) {
while ($count <= $row) {
echo'
<script>
$("#guild-join-request-'.$count.'").click (function() {
var guildName = $("#guild-join-name-'.$count.'").html();
var src = $("#guild-join-icn-'.$count.'").attr("src");
var iusername = "'.$iusername.'";
var userid = "'.$userid.'";
$.ajax({
url: "systems/join-guild.php",
type: "POST",
data: {iusername: iusername, userid: userid, guildName: guildName, src: src},
beforeSend: function() {
$("#sucess").html("Carregando...");
},
success: function (result) {
$("#user-painel-2").html(result);
} 
});
});
</script>'; 
echo'
<div id="guild-join-'.$count.'" class="guild-join">
<img src="'.$guild_icn.'" id="guild-join-icn-'.$count.'"></img>
<div id="guild-join-name-'.$count.'" class="guild-join-name">'.$guild_name.'</div>
<div id="guild-join-score-'.$count.'" class="guild-join-score" >- '.$score_result.'K</div>
<input style="cursor:pointer;" type="button" id="guild-join-request-'.$count.'" value="Solicitar" class="guild-join-request"></input>
<div id="guild-join-underline-'.$count.'" class="guild-join-underline"></div>
</div>';
$get = $select->fetch_array();
$guild_name = $get['guild'];
$guild_icn = $get['icn'];
$guild_score = $get['score'];
$score_result = round($guild_score/1000);
$count++;
}
echo'<menu id="guild-request">
<div id="guild-request-msg">Solicitações Pendentes:</div>'; 
#---------------------------------------------------------------
$select = $mysqli->query("SELECT * FROM `guild` WHERE `user_id` ='$userid'");
$row = $select->num_rows;
$get = $select->fetch_array();
$guild_name = $get['guild'];
$guild_icn = $get['icn'];
$guild_score = $get['score'];
$score_result = round($guild_score/1000);
$count = 1;
if($row > 0) {
while ($count <= $row) {
echo'
<div id="guild-request-'.$count.'" class="guild-request">
<img src="'.$guild_icn.'" id="guild-request-icn-'.$count.'"></img>
<div id="guild-request-name-'.$count.'" class="guild-request-name" >'.$guild_name.'</div>
<div id="guild-request-score-'.$count.'" class="guild-request-score">- '.$score_result.'K</div>
<button id="guild-request-cancel-'.$count.'" class="guild-request-cancel">Cancelar</button>
<div id="guild-request-underline-'.$count.'" class="guild-request-underline"></div>
</div>';
$get = $select->fetch_array();
$guild_name = $get['guild'];
$guild_icn = $get['icn'];
$guild_score = $get['score'];
$score_result = round($guild_score/1000);
$count++;
}
echo '</div>
</menu>';
} else {
echo'   
<menu id="guild-request">
<div id="guild-request-msg">Solicitações Pendentes:</div>
<div id="no-requests">Nenhuma solicitação pendente.</div>
</menu></menu>';    
}
#---------------------------------------------------------------
} else {
echo "Ainda não existe nenhuma guild."; 
}
} else {
echo "Erro.";   
}
php mysql

  • I didn’t understand very well... aren’t you managing to make the request disappear at click time? or it is as if the request had not been sent?

  • No, for example: the user clicks to request, registers the request in the BD, then when refreshing the page the option to request that Guild goes down there, and goes down as in the image, but only one result is missing, the rest continues.

  • Then that code from above, is the one that displays the requests and the one from below is the pending? You checked if you are sending the request?

  • I edited the question, I do not know if it has improved. The request is sent, registered. After updating only one result some of the Guilds options, the rest continues as request even if they have already been requested. The request is being sent normally, so much so that it appears down there, but as he has already requested it should disappear from up there... :/

  • It would be nice if you put it the way it is in the picture, her code. Because it’s hard to understand, but I know where the possible error might be, because I’m working with array as a result, and if that result results in multiple data, and I don’t use a repetition structure, Voce will simply take a record which is what seems to be happening

  • 1

    The method fetch_array of mysqli returns one result row as an associative, numeric or both array. That is, Voce is only taking the first result of its query. I think the fetch_all should be useful in your case.

  • I edited the whole question. That’s right Pantera, I need to transform the Rows in an array to pick up at the time of the other select, but I don’t know how to do, I don’t understand much about arrays...

Show 2 more comments

1 answer

3


Here are some modifications I suggest, so that your code works the way you want it to.

As already mentioned, the fetch_array returns only one query line or NULL if there is nothing more to be filled (either by the empty result or by having reached the end of the result of the rows returned by the base). If we use the fetch_all, which returns, by default, all query results in a numeric array, we can do as follows:

$select = $mysqli->query("SELECT * FROM `guild` WHERE `user_id`='$userid'");   
$guilds = $select->fetch_all();
$row = $select->num_rows;
if ($row > 0) {
  $guild = implode("', '", array_column($guilds, "guild"));
  $select = $mysqli->query("SELECT * FROM `guild` WHERE `guild` NOT IN('$guild') AND `user_id` != '$userid' ORDER BY `score` DESC LIMIT 5");

In this part of the code, we execute the query to get all guilds that the user is inserted/pending:

$select = $mysqli->query("SELECT * FROM `guild` WHERE `user_id`='$userid'");   
$guilds = $select->fetch_all();

Then we check the amount of result (if ($row > 0)), If it is greater than zero, we set up the new query to filter the guilds the user is not inserted/pending. Starting with the use of array_column to take only the value that interests us of the result (guild) and, together with the implode, we form a comma-separated string with all guilds that the user belongs/this pending:

$guild = implode("', '", array_column($guilds, "guild"));

For our Room, we will use the NOT IN() to filter through the guilds we don’t want in the resulting:

"SELECT * FROM `guild` WHERE `guild` NOT IN('$guild') AND `user_id` != '$userid' ORDER BY `score` DESC LIMIT 5"

Otherwise I believe you can keep your code like this, using the fetch_array. Complete code:

<?php
require_once ("includes/connection.php");
require_once ("includes/start-session.php");
require_once ("includes/encript.php");
$ip = $_SERVER["REMOTE_ADDR"];
$iusername = "Trabzera";
$userid = "10";
$select = $mysqli->query("SELECT * FROM `data` WHERE username='$iusername' AND `ip`='$ip'");
$row = $select->num_rows;
$get = $select->fetch_array();
if ($row > 0) {
$select = $mysqli->query("SELECT * FROM `guild` WHERE `user_id`='$userid'");   
$guilds = $select->fetch_all();
$row = $select->num_rows;
if ($row > 0) {
  $guild = implode("', '", array_column($guilds, "guild"));
  $select = $mysqli->query("SELECT * FROM `guild` WHERE `guild` NOT IN('$guild') AND `user_id` != '$userid' ORDER BY `score` DESC LIMIT 5");
  $row = $select->num_rows;
  $get = $select->fetch_array();
  $guild_name = $get['guild'];
  $guild_icn = $get['icn'];
  $guild_score = $get['score'];
  $score_result = round($guild_score/1000);
  $count = 1;
}
echo '<div id="guild-central">Central da Guild</div>
<div id="guild-central-msg">Selecione abaixo a opção desejada:</div>
<menu id="create-guild">
<div id="create-guild-msg">Criar uma Guild</div>
<div id="create-guild-txt">Para criar uma Guild é necessário alguns requerimentos:</div>
<div id="create-guild-req1">- Nível 10+</div>
<div id="create-guild-req2">- 1000 KP (Knautiluz Points)</div>
<div id="create-guild-reqok">Caso atenda esses requsistos clique abaixo:</div>
<button id="create-guild-button">Criar uma Guild</button>
</menu>';
echo '<menu id="join-guild">
<div id="join-guild-msg">Juntar-se a uma Guild</div>';
#-----------------------------------------------------
if ($row > 0) {
while ($count <= $row) {
echo'
<script>
$("#guild-join-request-'.$count.'").click (function() {
var guildName = $("#guild-join-name-'.$count.'").html();
var src = $("#guild-join-icn-'.$count.'").attr("src");
var iusername = "'.$iusername.'";
var userid = "'.$userid.'";
$.ajax({
url: "systems/join-guild.php",
type: "POST",
data: {iusername: iusername, userid: userid, guildName: guildName, src: src},
beforeSend: function() {
$("#sucess").html("Carregando...");
},
success: function (result) {
$("#user-painel-2").html(result);
} 
});
});
</script>'; 
echo'
<div id="guild-join-'.$count.'" class="guild-join">
<img src="'.$guild_icn.'" id="guild-join-icn-'.$count.'"></img>
<div id="guild-join-name-'.$count.'" class="guild-join-name">'.$guild_name.'</div>
<div id="guild-join-score-'.$count.'" class="guild-join-score" >- '.$score_result.'K</div>
<input style="cursor:pointer;" type="button" id="guild-join-request-'.$count.'" value="Solicitar" class="guild-join-request"></input>
<div id="guild-join-underline-'.$count.'" class="guild-join-underline"></div>
</div>';
$get = $select->fetch_array();
$guild_name = $get['guild'];
$guild_icn = $get['icn'];
$guild_score = $get['score'];
$score_result = round($guild_score/1000);
$count++;
}
echo'<menu id="guild-request">
<div id="guild-request-msg">Solicitações Pendentes:</div>'; 
#---------------------------------------------------------------
$select = $mysqli->query("SELECT * FROM `guild` WHERE `user_id` ='$userid'");
$row = $select->num_rows;
$get = $select->fetch_array();
$guild_name = $get['guild'];
$guild_icn = $get['icn'];
$guild_score = $get['score'];
$score_result = round($guild_score/1000);
$count = 1;
if($row > 0) {
while ($count <= $row) {
echo'
<div id="guild-request-'.$count.'" class="guild-request">
<img src="'.$guild_icn.'" id="guild-request-icn-'.$count.'"></img>
<div id="guild-request-name-'.$count.'" class="guild-request-name" >'.$guild_name.'</div>
<div id="guild-request-score-'.$count.'" class="guild-request-score">- '.$score_result.'K</div>
<button id="guild-request-cancel-'.$count.'" class="guild-request-cancel">Cancelar</button>
<div id="guild-request-underline-'.$count.'" class="guild-request-underline"></div>
</div>';
$get = $select->fetch_array();
$guild_name = $get['guild'];
$guild_icn = $get['icn'];
$guild_score = $get['score'];
$score_result = round($guild_score/1000);
$count++;
}
echo '</div>
</menu>';
} else {
echo'   
<menu id="guild-request">
<div id="guild-request-msg">Solicitações Pendentes:</div>
<div id="no-requests">Nenhuma solicitação pendente.</div>
</menu></menu>';    
}
#---------------------------------------------------------------
} else {
echo "Ainda não existe nenhuma guild."; 
}
} else {
echo "Erro.";   
}

Update

In case you have trouble with the method fetch_all (either because of the absence of the native Mysql driver on your server or because of the PHP version), you can go through the result with the fetch_assoc or the very fetch_array:

$select = $mysqli->query("SELECT * FROM `guild` WHERE `user_id`='$userid'");   
$guilds = [];
while ($guild = $select->fetch_array()) {
  $guilds[] = $guild['guild'];
}
if ($row > 0) {
  $guild = implode("', '", array_column($guilds, "guild"));
  $select = $mysqli->query("SELECT * FROM `guild` WHERE `guild` NOT IN('$guild') AND `user_id` != '$userid' ORDER BY `score` DESC LIMIT 5");

I hope I’ve helped.

  • +One for the whole explanation. But when I run the code the screen comes white, I realized that after fetch_all nothing else works, even if I give a var_dump($Guilds); It’s like after it freezes. You know what it can be?

  • 1

    @Jacksonantunes With its original code works normally?

  • Yes, it works, except for the part about removing the unwanted results. I don’t know exactly what the problem with the white screen, when Localhost tried to help me was the same thing, after fetch_all no command works, everything results in white screen. I tried to put in echo "Test"; after it but will not... var_dump also does not work.

  • 1

    To help debug, could you add these two lines at the top of your file? error_reporting(E_ALL);&#xA;ini_set("display_errors", 1); Apparently your PHP is not with the error display enabled.

  • Okay, I’m gonna try!

  • Whoops, now it’s: Fatal error: Call to Undefined method mysqli_result::fetch_all() in /home/u756477073/public_html/systems/Guild.php on line 14

  • 13 $Row = $select->num_rows; 14 $Guilds = $select->fetch_all(); are lines 13 and 14...

  • @Jacksonantunes can tell me the version of PHP he’s using?

  • My host says it’s version 5.5

  • 1

    Apparently the method fetch_all requires the msqlnd package (http://www.php.net/manual/en/mysqli-result.fetch-all.php#88031). Which operating system is using? So we can check if Voce already has this extension installed, and if we can not install.

  • Let’s go continue this discussion in chat.

Show 6 more comments

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.