mysql num row does not work for me

Question

mysql num row does not work for me

Navigation

#1 by (1 votes)

0

Hello, I need to add pages to my site. specifically it is a preview of a report of 3000 records and I want to show on each page 100 This is my query:

$sql ="SELECT documento.* FROM documento 
LEFT JOIN ubicacion ON (ubicacion.documento_id = documento.documento_id)
WHERE unidad_id IN(
   SELECT u.unidad_id FROM unidad AS u,estructura AS e 
   WHERE u.estructura_id = e.estructura_id 
   AND e.padre_estructura_id =3) 
AND estado_documento_id NOT LIKE '7'
GROUP BY documento_id limit 0,100"

and to do the total count of the documents I have this:

$cnt = mysql_num_rows(mysql_query($sql));

but when I look at the site, only 100 records appear and I can not change the page, only 1 of 1 appears when it should be around 3O pages.

this is the method:

  $cntp = ($cnt % $ipp > 0)+(int)($cnt / $ipp);

  print "<div align=center>" . $lan["page"] . " <select onchange='submit();' name=showfrom>";


  for ($i=0;$i<$cnt;$i+=$ipp)
  {
      $pn = 1+(int)($i/$ipp);
      print "<option value=$i ".($_POST['showfrom'] == $i ? "selected" :    "").">$pn</option>";
  }
  print "</select> " . $lan["of"] . " $cntp</div></form>";

mysql php

asked by Gabriel peña 17.03.2017 в 16:04

source

1 answer

Create in an ASP.NET MVC project Different contact form for each item listed [closed]

score 1 · Answer 1

You have to make an initial consultation to know the total number of documents:

$sql_conteo ="SELECT count(distinct documento_id) as total 
       FROM documento 
       LEFT JOIN ubicacion ON (ubicacion.documento_id = documento.documento_id)
       WHERE unidad_id IN (
             SELECT u.unidad_id 
             FROM unidad AS u,estructura AS e 
             WHERE u.estructura_id = e.estructura_id 
             AND e.padre_estructura_id =3) 
       AND estado_documento_id NOT LIKE '7'";

That will give you the number of pages to feed your pager, and that query is constant (because the pager always has the same total of pages, regardless of the page you are looking at)

And then make your query to get the 100 records of the current page

$sql ="SELECT documento.* FROM documento  
       FROM documento 
       LEFT JOIN ubicacion ON (ubicacion.documento_id = documento.documento_id)
       WHERE unidad_id IN (
             SELECT u.unidad_id 
             FROM unidad AS u,estructura AS e 
             WHERE u.estructura_id = e.estructura_id 
             AND e.padre_estructura_id =3) 
       AND estado_documento_id NOT LIKE '7'
       GROUP BY documento_id limit $desde,$hasta";

Without knowing more about your code, I can not tell you much more, except that:

It is not good practice to use implicit joins (as you do in your derived table)
You can probably rewrite the condition IN by doing a JOIN with unit and structure
I interpolated the variables $ from and $ up assuming you get them from $ _POST
It is a bad idea to interpolate variables in this way but the prepared sentences are beyond the scope of this question