Return SQL query as array

Question

The reserva_disponibilidade return the same Array(29), when should return...29,30, 31... Is there a way to fix this?

    function estoque($data, $dias, $tipo) {

        //Ajusta a data...
        $inicio = strtotime($data);
        $edia = date('d', $inicio); 
        $emes = date('m', $inicio);
        $eano = date('Y', $inicio);

        //Conecta ao db...
        $db = new DBConfig();
        $db->config();
        $db->conn();

        //Arrays
        $smarty_array = array();
        $smarty_data_array = array();

        $query_quartos = mysql_query("SELECT * FROM quartos AS quartos 
                                        INNER JOIN tipos AS tipos 
                                        LEFT JOIN reservas AS reservas 
                                        ON quartos.quarto_tipo = tipos.tipo_id
                                        AND quartos.quarto_numero = reservas.reserva_quarto_id
                                        WHERE quartos.quarto_tipo = '".$tipo."'
                                        GROUP BY quartos.quarto_id HAVING Count(*) >= 1") or die(mysql_error());
        while($row = mysql_fetch_assoc($query_quartos)){
            $quartoid = $row["quarto_id"];

            while($i <= $dias) {
                $today = strtotime(date('Y-m-d',mktime(0,0,0,date($emes),date($edia)+$i,date($eano))));
                $query = mysql_query("SELECT * FROM quartos AS quartos 
                              LEFT JOIN reservas AS reservas 
                              ON quartos.quarto_numero = reservas.reserva_quarto_id
                              AND ".$today." BETWEEN reservas.reserva_checkin AND reservas.reserva_checkout
                              WHERE quartos.quarto_id = '".$quartoid."'
                              GROUP BY quartos.quarto_id HAVING Count(*) >= 1") or die(mysql_error());
                while($rowe = mysql_fetch_assoc($query)){
                    if (empty($rowe["reserva_status"])) {
                        $rowe["reserva_status"] = "0";
                    }
                    $smarty_data_array[] = $rowe["reserva_status"];
                }
                $i++;
            }

            $row["reserva_disponibilidade"] = $smarty_data_array;
            $smarty_array[] = $row;
        }

        $db->close();
        return $smarty_array;
    }

Output

Smarty_Variable Object (3)
->value = Array (3)
  0 => Array (16)
    quarto_id => "12"
    quarto_tipo => "1"
    quarto_numero => "1"
    quarto_descricao => "Quarto padrão"
    tipo_id => "1"
    tipo_nome => "Standard"
    tipo_foto => "03f9efa3b682512c74a9275656622b03.jpg"
    reserva_id => "2"
    reserva_quarto_id => "1"
    reserva_valor => "400"
    reserva_status => "2"
    reserva_cliente_id => "58"
    reserva_checkin => "1303794000"
    reserva_checkout => "1303966800"
    reserva_obs => "Aguardando pagamento"
    reserva_disponibilidade => Array (29)
      0 => "0"
      1 => "0"
      2 => "0"
      3 => "0"
      4 => "0"
      5 => "0"
      6 => "0"
      7 => "0"
      8 => "0"
      9 => "0"
      10 => "0"
      11 => "0"
      12 => "2"
      13 => "2"
      14 => "2"
      15 => "0"
      16 => "0"
      17 => "0"
      18 => "0"
      19 => "0"
      20 => "0"
      21 => "0"
      22 => "0"
      23 => "0"
      24 => "0"
      25 => "0"
      26 => "0"
      27 => "0"
      28 => "0"
  1 => Array (16)
    quarto_id => "15"
    quarto_tipo => "1"
    quarto_numero => "5"
    quarto_descricao => "Deluxe "
    tipo_id => "1"
    tipo_nome => "Standard"
    tipo_foto => "03f9efa3b682512c74a9275656622b03.jpg"
    reserva_id => null
    reserva_quarto_id => null
    reserva_valor => null
    reserva_status => null
    reserva_cliente_id => null
    reserva_checkin => null
    reserva_checkout => null
    reserva_obs => null
    reserva_disponibilidade => Array (29)
      0 => "0"
      1 => "0"
      2 => "0"
      3 => "0"
      4 => "0"
      5 => "0"
      6 => "0"
      7 => "0"
      8 => "0"
      9 => "0"
      10 => "0"
      11 => "0"
      12 => "2"
      13 => "2"
      14 => "2"
      15 => "0"
      16 => "0"
      17 => "0"
      18 => "0"
      19 => "0"
      20 => "0"
      21 => "0"
      22 => "0"
      23 => "0"
      24 => "0"
      25 => "0"
      26 => "0"
      27 => "0"
      28 => "0"
  2 => Array (16)
    quarto_id => "26"
    quarto_tipo => "1"
    quarto_numero => "80"
    quarto_descricao => "Quarto novo "
    tipo_id => "1"
    tipo_nome => "Standard"
    tipo_foto => "03f9efa3b682512c74a9275656622b03.jpg"
    reserva_id => null
    reserva_quarto_id => null
    reserva_valor => null
    reserva_status => null
    reserva_cliente_id => null
    reserva_checkin => null
    reserva_checkout => null
    reserva_obs => null
    reserva_disponibilidade => Array (29)
      0 => "0"
      1 => "0"
      2 => "0"
      3 => "0"
      4 => "0"
      5 => "0"
      6 => "0"
      7 => "0"
      8 => "0"
      9 => "0"
      10 => "0"
      11 => "0"
      12 => "2"
      13 => "2"
      14 => "2"
      15 => "0"
      16 => "0"
      17 => "0"
      18 => "0"
      19 => "0"
      20 => "0"
      21 => "0"
      22 => "0"
      23 => "0"
      24 => "0"
      25 => "0"
      26 => "0"
      27 => "0"
      28 => "0"
->nocache = false
->scope = "Smarty root"

TL;DR; anyway, what's this: $row["reserva_disponibilidade"] = $smarty_data_array; shouldn't they be reversed? this way you're assignig the $row key the whole smart_data_array..Sorry if I'm wrong, I'm a bit tired and sleepy LOL — Damien Pirsy
– Damien Pirsy, Commented Apr 14, 2011 at 20:50
I think that is correct, the data is returning correctly, but is repeating... — Hbug
– Hbug, Commented Apr 14, 2011 at 20:55
One thing I'd say, putting an SQL query operation in a loop? Bad idea if you can avoid it. Ditto for your timestamp generation logic. The timestamp doesn't (or at least shouldn't) change during the loop, so you can just generate it once before entering the loop. As for the SQL query, using IN instead of = means you can match against a list of values for a field instead of a single one. You can use a loop to build the list, then run a single query to fetch all the data. It will almost certainly be a lot faster. — GordonM
– GordonM, Commented Apr 14, 2011 at 20:55
I have no sucess with a single query before, but is a good idea. — Hbug
– Hbug, Commented Apr 14, 2011 at 21:01

DaOgre · Accepted Answer · 2011-04-14 23:31:55Z

The issue here is with when you assign the value for $quartoid. If you look at just this section of code:

while($row = mysql_fetch_assoc($query_quartos)){
        $quartoid = $row["quarto_id"];

        while($i <= $dias) {
            $today = strtotime(date('Y-m-d',mktime(0,0,0,date($emes),date($edia)+$i,date($eano))));
            $query = mysql_query("SELECT * FROM quartos AS quartos 
                          LEFT JOIN reservas AS reservas 
                          ON quartos.quarto_numero = reservas.reserva_quarto_id
                          AND ".$today." BETWEEN reservas.reserva_checkin AND reservas.reserva_checkout
                          WHERE quartos.quarto_id = '".$quartoid."'
                          GROUP BY quartos.quarto_id HAVING Count(*) >= 1") or die(mysql_error());
            while($rowe = mysql_fetch_assoc($query)){
                if (empty($rowe["reserva_status"])) {
                    $rowe["reserva_status"] = "0";
                }
                $smarty_data_array[] = $rowe["reserva_status"];
            }
            $i++;
        }

Your second while loop that begins with while($i <= $dias) { has the exact same SQL query inside of it, executing $dias times. The values for your variables will never change during this iteration as $today and $quartoid are going to be the same. I hope that answered your question, sorry if I totally missed on the problem, your row naming was vaguely confusing to me :)

Collectives™ on Stack Overflow

Return SQL query as array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related