douchushao7799
2018-08-16 18:26 阅读 43
已采纳

由于需要参数行,无法找到查询未返回结果的原因[重复]

I've been following a tutorial for a project and there's a part where pagination is implemented. Even following step by step the tutorial, I'm receiving a "mysqli_fetch_assoc()" error.

I've been searching here about the error received, and everything points out to a bad sql query executed. This is error:

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in .../manage.php on line 40

There are lots of questions and different answers for this error. I've been trying lots of solutions but now I'm kind of frustrated and desperate.

I'd like to know if you could check the attached files, re-produce the whole code and check if you receive the error as well, or if you get successfully the array without the error code, and feedback me what might be causing the tormentors error and how to correct it.

Please lads, be kind, I'm still learning, and by my own. Out there, in previous questions I've been receiving comments like "you want us to do your code", "we won't do it for you" and others, if that comes to your mind, please avoid to type anything. Those comments don't really help to anybody. manage.php

    <?php

/**
*
*/
class Manage
{

    private $con;

    function __construct()
    {
        include_once("../database/db.php");//regular db connexion
        $db = new Database();
        $this->con = $db->connect();
    }

    public function manageRecordWithPagination($table,$pno){
        $a = $this->pagination($this->con,$table,$pno,5);
        if ($table == "categories") {
            $sql = "SELECT p.cid,p.category_name as category, c.category_name as parent, p.status FROM categories p LEFT JOIN categories c ON p.parent_cat=c.cid ".$a["limit"];
        }else if($table == "products"){
            $sql = "SELECT p.pid,p.product_name,c.category_name,b.brand_name,p.product_price,p.product_stock,p.added_date,p.p_status FROM products p,brands b,categories c WHERE p.bid = b.bid AND p.cid = c.cid ".$a["limit"];
        }else{
            $sql = "SELECT * FROM ".$table." ".$a["limit"];
        }
        $result = $this->con->query($sql) or die($this->con->error);
        $rows = array();
        if($result->num_rows > 0){
            while ($row = $result->fetch_assoc()) {
                $rows[] = $row;
            }
        }
        return ["rows"=>$rows,"pagination"=>$a["pagination"]];

    }

    private function pagination($con,$table,$pno,$n){
        $query = $con->query("SELECT COUNT(*) as rows FROM " .$table);
        $row = mysqli_fetch_assoc($query);
        //$totalRecords = 100000;
        $pageno = $pno;
        $numberOfRecordsPerPage = $n;

        $last = ceil($row['rows']/$numberOfRecordsPerPage);

        $pagination = "<ul class='pagination'>";

        if ($last != 1) {
            if ($pageno > 1) {
                $previous = "";
                $previous = $pageno - 1;
                $pagination .= "<li class='page-item'><a class='page-link' pn='".$previous."' href='#' style='color:#333;'> Previous </a></li></li>";
            }
            for($i=$pageno - 5;$i< $pageno ;$i++){
                if ($i > 0) {
                    $pagination .= "<li class='page-item'><a class='page-link' pn='".$i."' href='#'> ".$i." </a></li>";
                }

            }
            $pagination .= "<li class='page-item'><a class='page-link' pn='".$pageno."' href='#' style='color:#333;'> $pageno </a></li>";
            for ($i=$pageno + 1; $i <= $last; $i++) {
                $pagination .= "<li class='page-item'><a class='page-link' pn='".$i."' href='#'> ".$i." </a></li>";
                if ($i > $pageno + 4) {
                    break;
                }
            }
            if ($last > $pageno) {
                $next = $pageno + 1;
                $pagination .= "<li class='page-item'><a class='page-link' pn='".$next."' href='#' style='color:#333;'> Next </a></li></ul>";
            }
        }
    //LIMIT 0,10
        //LIMIT 20,10
        $limit = 'LIMIT '.($pageno - 1) * $numberOfRecordsPerPage.','.$numberOfRecordsPerPage;

        return ['pagination'=>$pagination,'limit'=>$limit];
    }
}

$obj = new Manage();
echo "<pre>";
print_r($obj->manageRecordWithPagination("categories",1));
?>

And this is the database

--
-- Database: `project_inv`
--

-- --------------------------------------------------------

--
-- Table structure for table `brands`
--

CREATE TABLE `brands` (
  `bid` int(11) NOT NULL,
  `brand_name` varchar(255) NOT NULL,
  `status` enum('1','0') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `brands`
--

INSERT INTO `brands` (`bid`, `brand_name`, `status`) VALUES
(1, 'iPhone', '1'),
(2, 'Samsung', '1'),
(3, 'Mac', '1'),
(4, 'HP', '1'),
(5, 'Adobe', '1'),
(6, 'Huawei', '1'),
(7, 'Linksys', '1');

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `cid` int(11) NOT NULL,
  `parent_cat` int(11) NOT NULL,
  `category_name` varchar(255) NOT NULL,
  `status` enum('1','0') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`cid`, `parent_cat`, `category_name`, `status`) VALUES
(1, 0, 'Electronics', '1'),
(2, 0, 'Gadgets', '1'),
(3, 0, 'Software', '1'),
(4, 1, 'Mobiles', '1'),
(5, 0, 'Antivirus', '1'),
(6, 1, 'Laptop', '1');

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `pid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `bid` int(11) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_price` double NOT NULL,
  `product_stock` int(11) NOT NULL,
  `added_date` date NOT NULL,
  `p_status` enum('1','0') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`pid`, `cid`, `bid`, `product_name`, `product_price`, `product_stock`, `added_date`, `p_status`) VALUES
(1, 4, 1, 'iPhone X', 1000000, 1000, '2018-08-10', '1'),
(2, 4, 1, 'iPhone 8+', 700000, 1000, '2018-08-11', '1'),
(3, 4, 1, 'iPhone 8', 600000, 1000, '2018-08-11', '1'),
(5, 4, 1, 'iPhone 7+', 600000, 1000, '2018-08-11', '1'),
(6, 6, 3, 'Macbook Pro 15\" Touch Bar ', 2000000, 500, '2018-08-11', '1');

-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(300) NOT NULL,
  `usertype` enum('Admin','Other') NOT NULL,
  `register_date` date NOT NULL,
  `last_login` datetime NOT NULL,
  `notes` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id`, `username`, `email`, `password`, `usertype`, `register_date`, `last_login`, `notes`) VALUES
(1, 'Luis', 'lvargas@lita.com', '$2y$08$b6i3cJt8n..e6DQP3xF/5.y1n2IGIm12nTKHhMgkrq3MOMVbgwVrS', 'Admin', '2018-08-07', '2018-08-11 04:08:42', ''),
(2, 'Test', 'test@lita.com', '$2y$08$By8MzUIFcL3bnhD2PoNmDulOyrCOLUE9oriowPhIfX38Znq4E3NFa', 'Admin', '2018-08-07', '2018-08-09 02:08:35', ''),
(3, 'Diego Robles', 'diegor@lita.com', '$2y$08$Q.0euaKDn0ONUPxcpzDChud5dTFSUCaInlF0L0Hg3IcQ9I/u.rjyK', 'Admin', '2018-08-09', '2018-08-09 00:00:00', '');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `brands`
--
ALTER TABLE `brands`
  ADD PRIMARY KEY (`bid`),
  ADD UNIQUE KEY `brand_name` (`brand_name`);

--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`cid`) USING BTREE,
  ADD UNIQUE KEY `category_name` (`category_name`),
  ADD UNIQUE KEY `cid` (`cid`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`pid`),
  ADD UNIQUE KEY `product_name` (`product_name`),
  ADD KEY `cid` (`cid`),
  ADD KEY `bid` (`bid`);

--
-- Indexes for table `user`
--
ALTER TABLE `user`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);
--
-- Constraints for table `products`
--
ALTER TABLE `products`
  ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `categories` (`cid`),
  ADD CONSTRAINT `products_ibfk_2` FOREIGN KEY (`bid`) REFERENCES `brands` (`bid`);
COMMIT;

That should display an array, like in the image called "good.png" and not like the image "wrong.png"; which is mine.

good

wrong

</div>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    dongou1970 dongou1970 2018-08-16 19:34

    rows is a reserved keyword as of MySQL 8.0.2. Reserved words must be surrounded in backticks, though single quotes can work as column aliases:

    $query = $con->query("SELECT COUNT(*) as `rows` FROM " .$table);
    
    点赞 评论 复制链接分享

相关推荐