BottledTurtles BottledTurtles - 5 months ago 10
SQL Question

Count records and print rows

I want mysql to select the record which has

SECRETKEY = 'ts17'
then count records in
tb_dailyshow
with
SECRETKEY = 'ts17'
AND
DATE = '20160608'
(if all the records in
tb_dailyshow
are less than
10
print
NAME
row from
tb_names
table).

First table
tb_names


ID | SECRETKEY | NAME
---------------------
1 | ts17 | Chris


Second table
tb_dailyshow


ID | SECRETKEY | DATE
--------------------
1 | ts17 | 20160608
2 | ts17 | 20160608
3 | fa99 | 20160608


my mysql line:

SELECT n.*
FROM tb_adv n
INNER JOIN tb_shown ds
ON ds.uid = n.uid
AND ds.date = '20160607'
WHERE n.uid = '575474d715154'
GROUP BY n.id
HAVING COUNT(*)<10


php codes:

$q = "SELECT n.*
FROM tb_adv n
INNER JOIN tb_shown ds
ON ds.uid = n.uid
AND ds.date = '20160607'
WHERE n.uid = '575474d715154'
GROUP BY n.id
HAVING COUNT(*)<10";
$select = mysqli_query($con, $q);
while($row = mysqli_fetch_array($select)){
echo $row['text'];
}

Answer

http://sqlfiddle.com/#!9/148e1e/1

SELECT n.*
FROM tb_names n
INNER JOIN tb_dailyshow ds
ON ds.secretkey = n.secretkey
  AND ds.date = '20160608'
WHERE n.secretkey = 'ts17'
GROUP BY n.id
HAVING COUNT(*)<10

To debug your code transform this part:

$select = mysqli_query($con, $q);
while($row = mysqli_fetch_array($select)){
  echo $row['text'];
}

to this:

if($select = mysqli_query($con, $q)) {
  while($row = mysqli_fetch_array($select)){
    echo $row['text'];
  }
} else {
  echo "Query failed with error: ".mysqli_error($conn);
}