Mysqli error

Question

UK-1991 0 Posting Whiz

9 Years Ago

function view_report($dateval1, $dateval2, $id) {
        global $connection;

        $query_get      = "SELECT * FROM presence WHERE p_date BETWEEN {$dateval1} AND {$dateval2} ORDER BY DATE";
        $query_confirm  = mysqli_query($connection, $query_get);

        if(isset($_POST["show"])) {

            while($record = mysqli_fetch_assoc($query_confirm)) {

                $output  = "<tr>";
                $output .= "<td>" . $record["p_date"] . "</td>";
                $output .= "<td>" . $record["p_day"] . "</td>";
                $output .= "<td>" . $record["timein"] . "</td>";
                $output .= "<td>" . $record["timeout"] . "</td>";
                $output .= "<td>" . "06:25:22" . "</td>";
                $output .= "<td>" . $record["p_date"] . "</td>";
                $output .= "</tr>";
                return $output; 
            }   
        }
    }

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\attendance\includes\functions.php on line 90

can anyone help me out and when i remove all query and write only select * from presence it returns only 1 query

mysql php

7 Contributors
16 Replies
154 Views
2 Days Discussion Span
Latest Post 9 Years Ago Latest Post by Gideon_1

pzuurveen 90 Posting Whiz in Training

9 Years Ago

2e part:

       $output ="";
           while($record = mysqli_fetch_assoc($query_confirm)) {
                $output .= "<tr>";
                $output .= "<td>" . $record["p_date"] . "</td>";
                $output .= "<td>" . $record["p_day"] . "</td>";
                $output .= "<td>" . $record["timein"] . "</td>";
                $output .= "<td>" . $record["timeout"] . "</td>";
                $output .= "<td>" . "06:25:22" . "</td>";
                $output .= "<td>" . $record["p_date"] . "</td>";
                $output .= "</tr>";
                }   
            return $output;

not sure about the 1st part.
Are $dateval1 eand $dateval2 in the proper format?
how is p_date defined? date? dateTime?

Zagga

9 Years Ago

Hi,

Add some error trapping to your query and see if it sheds any more light ...
$query_confirm = mysqli_query($connection, $query_get) or die(mysqli_error($connection));

lps 71 Posting Pro in Training

9 Years Ago

Based on your previous threads, I assume your p_date still is 'date' format in database. You can try @Zagga's idea by adding the die() to show the error, but now your problem is the query. Try out
SELECT * FROM presence WHERE p_date BETWEEN '$dateval1' AND '$dateval2' ORDER BY p_date main problem should be 'DATE' is not exist in the table.

diafol

9 Years Ago

Think I mentioned before, globals bad, just pass the connection.
Also use prepared statements if using input data.
pz has the return in the loop issue solved
Good advice from Zagga wrt errors

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

UK-1991 0 Posting Whiz · Answer 1 · 2015-05-19T23:31:57+00:00

@diafol

yes you metioned me about that and I also went through it but was having some dificulties in doing that I will post a thread for that.

@lps

the date column exist as p_date is the date column

I modified my code and placed it on the main here is the code and let me know where i am doing mistake though

<form name="form1" method="POST" action="status_rep.php?show=1">
                <label>
                    <p>Select Month</p>
                    <p class="info">

                        <select name="month">
                            <option value="1">January</option>
                            <option value="2">February</option>
                            <option value="3">March</option>
                            <option value="4">April</option>
                            <option value="5">May</option>
                            <option value="6">June</option>
                            <option value="7">July</option>
                            <option value="8">August</option>
                            <option value="9">September</option>
                            <option value="10">October</option>
                            <option value="11">November</option>
                            <option value="12">December</option>
                        </select>
                    </p>
                </label>

                <div class="clear"></div>

                <label>
                    <p>Select Year</p>
                    <p class="info">
                        <select name="year">
                            <option value="2014">2014</option>
                            <option value="2015">2015</option>
                            <option value="2016">2016</option>
                            <option value="2017">2017</option>
                            <option value="2018">2018</option>
                            <option value="2019">2019</option>
                            <option value="2020">2020</option>
                            <option value="2021">2021</option>
                            <option value="2022">2022</option>
                            <option value="2023">2023</option>
                            <option value="2024">2024</option>
                            <option value="2025">2025</option>
                            <option value="2026">2026</option>
                        </select>
                    </p>
                </label>



                <div class="input" style="text-align:center;">
                    <p>&nbsp;</p>             
                    <input type="submit" value="View" class="btn1" name="show" />
                </div>
                </form>
                <div class="clear"></div>

                <?php

                    if($_GET["show"] == 1) {
                        $month = intval($_POST["month"]);
                        $year  = intval($_POST["year"]);

                    } else {
                        $month = date("m");
                        $year  = date("Y"); 
                    }

                    echo "<script language = 'javascript'>
                             form1.month.options[",$month-1,"].selected = true;
                             form1.year.options[",$year-2014,"].selected = true;
                         </script>";

                        $startday = 1;

                        if($month==1 || $month==3 || $month==5 || $month==7 || $month==8 || $month==10 || $month==12) {
                            $endday = 31;
                        }
                        if($month==4 || $month==6 || $month==9 || $month==11) {
                            $endday = 30;
                        }
                        if($month==2) {
                            if($year%4==0) {
                                $endday = 29;
                            }else {
                                $endday = 28;
                            }
                        }

                    $date1 = $year . "-" . $month . "-" . $startday;
                    $date2 = $year . "-" . $month . "-" . $endday;
                ?>
                <div id="details">
                    <table>
                        <tr>
                            <th>Date</th>
                            <th>Day</th>
                            <th>Time In</th>
                            <th>Time Out</th>
                            <th>Allowed Clock In</th>
                            <th>Shift</th>
                        </tr>



                        <?php 

                        $id = 3;

                        $query_get      = "SELECT * FROM presence WHERE user_id = {$id} AND p_date BETWEEN {$date1} AND {$date2} ORDER BY DATE";
                        $query_confirm  = mysqli_query($connection, $query_get);

                        if(isset($_POST["show"])) {     
                            while($record = mysqli_fetch_array($query_confirm)) {
                                echo "<tr>";
                                echo "<td>" . $record["p_date"] . "</td>";
                                echo "<td>" . $record["p_day"] . "</td>";
                                echo "<td>" . $record["timein"] . "</td>";
                                echo "<td>" . $record["timeout"] . "</td>";
                                echo "<td>" . "06:25:22" . "</td>";
                                echo "<td>" . $record["p_date"] . "</td>";
                                echo "</tr>";
                            }   
                        }
                        //echo view_report() 
                        //$date1, $date2, 3 
                        ?>
                    </table>             
                </div>

UK-1991 0 Posting Whiz · Answer 2 · 2015-05-19T23:37:18+00:00

pre_id | timein   | timeout  | p_date     | p_day  | shift | user_id
  65   | 04:45:10 | 00:00:00 | 2015-03-23 | Monday | eve   |   3

Gideon_1 15 Junior Poster · Answer 3 · 2015-05-20T05:29:00+00:00

Your are still using ORDER BY DATE instead of p_date. Also you can save you fetch_assoc to an array and use fetch assoc to ouput the data which will be relevantly fast than echoing every thing bit by bit when dealing with huge amount of data.

UK-1991 0 Posting Whiz · Answer 4 · 2015-05-20T09:21:58+00:00

@Gideon_1 Yes sory changed it and now nothing no results found like nothing pulls up though. Just a blank listings.

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 5 · 2015-05-20T10:15:43+00:00

pritaeas 2,194 ¯\_(ツ)_/¯

9 Years Ago

You are missing single quotes around your dates.

Gideon_1 15 Junior Poster · Answer 6 · 2015-05-20T10:26:29+00:00

You have made many mistakes of which include
1. Setting Form action to POST but using GET in PHP
2. Did not initialize mysqli connection
3. Were not sorting output by any column but instead you used a function.

Try these codes and make changes to it so it matches with your credentials

<form name="form1" method="POST" action="status_rep.php">
    <label>
        <p>Select Month</p>
        <p class="info">
            <select name="month">
                <option value="1">January</option>
                <option value="2">February</option>
                <option value="3">March</option>
                <option value="4">April</option>
                <option value="5">May</option>
                <option value="6">June</option>
                <option value="7">July</option>
                <option value="8">August</option>
                <option value="9">September</option>
                <option value="10">October</option>
                <option value="11">November</option>
                <option value="12">December</option>
            </select>
        </p>
    </label>

    <div class="clear"></div>

    <label>
        <p>Select Year</p>
        <p class="info">
            <select name="year">
                <option value="2014">2014</option>
                <option value="2015">2015</option>
                <option value="2016">2016</option>
                <option value="2017">2017</option>
                <option value="2018">2018</option>
                <option value="2019">2019</option>
                <option value="2020">2020</option>
                <option value="2021">2021</option>
                <option value="2022">2022</option>
                <option value="2023">2023</option>
                <option value="2024">2024</option>
                <option value="2025">2025</option>
                <option value="2026">2026</option>
            </select>
        </p>
    </label>

    <div class="input" style="text-align:center;">
        <input type="submit" value="View" class="btn1" name="show" />
    </div>
</form>

<div class="clear"></div>

<?php
    if(isset($_POST['month']) && isset($_POST['year'])) {
        $month = intval($_POST["month"]);
        $year  = intval($_POST["year"]);
    } else {
        $month = date("m");
        $year  = date("Y"); 
    }


    echo "<script language = 'javascript'>
             form1.month.options[",$month-1,"].selected = true;
             form1.year.options[",$year-2014,"].selected = true;
         </script>";


    $startday = 1;

    //endday for months with 31 days
    if($month==1 || $month==3 || $month==5 || $month==7 || $month==8 || $month==10 || $month==12) {
        $endday = 31;
    }

    //endday for months with 30 days
    if($month==4 || $month==6 || $month==9 || $month==11) {
        $endday = 30;
    }

    //endday for february
    if($month==2) {
        if($year%4==0) {
            $endday = 29;
        }else {
            $endday = 28;
        }
    }
    $date1 = $year . "-" . $month . "-" . $startday;
    $date2 = $year . "-" . $month . "-" . $endday;
?>


<div id="details">
    <table>
        <thead>
            <tr>
                <th>Date</th>
                <th>Day</th>
                <th>Time In</th>
                <th>Time Out</th>
                <th>Allowed Clock In</th>
                <th>Shift</th>
            </tr>
        </thead>

        <tbody>
            <?php 
                //setting database credentials, change it to your own
                $db_host     = '127.0.0.1';//same as localhost
                $db_user     = 'root';
                $db_password = '';
                $db_name     = 'test';//provide your database name

                //initializing connection
                $connection = mysqli_connect ($db_host, $db_user, $db_password, $db_name);

                //query
                $id = 3;
                $query_get      = "SELECT * FROM presence WHERE user_id = {$id} AND p_date BETWEEN {$date1} AND {$date2} ORDER BY p_date";
                $query_confirm  = mysqli_query($query_get);

                //checking for the number of returned rows, if not null or equal to zero then run the codes
                if(mysqli_num_rows($query_get)) {     
                    while($record = mysqli_fetch_assoc ($query_confirm)) {
                        //helping to increase output speed when there are lots of data to deal with
                ?>
                    <tr>
                        <td><?php echo $record["p_date"]; ?></td>
                        <td><?php echo $record["p_day"]; ?></td>
                        <td><?php echo $record["timein"]; ?></td>
                        <td><?php echo $record["timeout"]; ?></td>
                        <td><?php echo "06:25:22"; ?></td>
                        <td><?php echo $record["p_date"]; ?></td>
                    </tr>
                <?php
                        }   
                    }
                ?>
            //echo view_report() 
            //$date1, $date2, 3 
            ?>
        </tbody>
    </table>             
</div>

UK-1991 0 Posting Whiz · Answer 7 · 2015-05-20T10:45:46+00:00

Still the same I don't know why I am getting blank no results

Gideon_1 15 Junior Poster · Answer 8 · 2015-05-20T11:00:15+00:00

try echoing out the mysqli_num_rows($query_get)

UK-1991 0 Posting Whiz · Answer 9 · 2015-05-20T11:07:26+00:00

Runs perfect

$query_get      = "SELECT * FROM presence";

and with num rows echo 10

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 10 · 2015-05-20T11:08:24+00:00

You are still missing the single quotes around your dates.

UK-1991 0 Posting Whiz · Answer 11 · 2015-05-20T11:38:08+00:00

litraley literaly 1 single very tiny mistake can led you to a very distrubance of your mmind though thank you very much @pritaeas thumbs up for you

Gideon_1 15 Junior Poster · Answer 12 · 2015-05-20T11:41:42+00:00

Yah, your dates can also be problem. use

    $date1 = "{$year}-{$month}-{$startday}";
    $date2 = "{$year}-{$month}-{$endday}";

so try the sql

SELECT * FROM presence WHERE user_id = {$id} AND p_date BETWEEN {$date1} AND {$date2} ORDER BY p_date

Then echo out mysqli_num_rows