如何使用where and like语句从表中选择数据并回显它 - How to select a data from a table using the where and like statement and echo it

How to select a data from a table using the where and like statement and echo it

我有一个问题，如何从数据库表中选择一个数据使用were和like语句。

$hy=mysql_query("select  (Total) AS firstterm 
                 FROM studentmark, subject 
                 where studentmark.student_id='$name' 
                   AND studentmark.YEAR='$ya' 
                   AND subject.code=studentmark.code    
                   AND studentmark.TERM='$term' LIKE 'F%'");
$hm=mysql_num_rows($hy);
$fetch=mysql_fetch_array($hy);
echo $fetch['firstterm'];

问题是，表中没有选择Total为89的项中的LIKE 'F%' (FIRST)，而是选择了Total为73的项中的LIKE 'S%' (SECOND)。我还缺什么吗?

下面的表

TERM   | CODE |student_id|contAss20Asg|ClassWk10 |Test2nd10|YEAR |EXAM| TOTAL
FIRST  | AGR  | John     |  18        |5         |   7     |2011 | 59 |   89
SECOND |AGR2  |John      |  13        |6         |   4     |2011 | 40 |   73
THIRD  |AGR3  |John      |  18        |6         |   8     |2011 | 34 |   64
FIRST  |BIO   |John      |  12        |3         |   3     |2011 | 55 |   73
SECOND |BIO2  |John      |  14        |8         |   7     |2011 | 56 |   85
THIRD  |BIO3  |John      |  12        |8         |   8     |2011 | 42 |   70

我的代码如下

<?php echo '</td><td>'?>
  <?php 
    if ($fetch['Total']==NULL){
echo 'missed';
}else 
    $hy=mysql_query("select  (Total) AS secondterm FROM studentmark, subject where studentmark.student_id='$name' AND studentmark.YEAR='$ya' AND subject.code=studentmark.code    AND studentmark.TERM='$term' LIKE 'S%'");
$hm=mysql_num_rows($hy);
$fetch=mysql_fetch_array($hy);
echo $fetch['secondterm'];
?>
<?php echo '</td><td>'?>
  <?php 
    if ($fetch['Total']==NULL){
}else 
    $hy=mysql_query("select  (Total) AS firstterm FROM studentmark, subject where studentmark.student_id='$name' AND studentmark.YEAR='$ya' AND subject.code=studentmark.code    AND studentmark.TERM='$term' LIKE 'F%'");
$hm=mysql_num_rows($hx);
$hm=mysql_num_rows($hy);
$row=mysql_fetch_array($hy);
echo $row['secondterm'];

?>
<?php echo '</td><td>'?>
  <?php 
    if ($fetch['Total']==NULL){
//echo 'missed';
}else 
    $hy=mysql_query("select  (Total) AS thirdterm FROM studentmark, subject where studentmark.student_id='$name' AND studentmark.YEAR='$ya' AND subject.code=studentmark.code    AND studentmark.TERM='$term'");
$hm=mysql_num_rows($hy);
$hm=mysql_num_rows($hy);
$fetch=mysql_fetch_array($hy);
$row=mysql_fetch_array($hy);
echo $fetch['firstterm']+ $row['secondterm'] + $fetch['thirdterm'];
?>

LIKE是用于比较的操作符，您需要像使用'='，'>'等操作符一样使用它。

studentmark.TERM LIKE '%someval%';

希望这能回答你的问题。

类似的语法是错误的，所以试试这个

SELECT TOTAL AS firstterm 
FROM studentmark, subject 
WHERE studentmark.student_id='$name' 
  AND subject.code=studentmark.code   
  AND studentmark.YEAR='$ya' 
  AND studentmark.TERM LIKE 'F%'");

最好也使用JOIN语法编码，就像这样

SELECT TOTAL AS firstterm 
FROM studentmark
   JOIN subject ON subject.code=studentmark.code
WHERE studentmark.student_id='$name' 
  AND studentmark.YEAR='$ya' 
  AND studentmark.TERM LIKE 'F%'");

如果他们发明了一个不能产生正确响应的FOURTH项，会发生什么呢?由于您的TERM列似乎被形式化为FIRST, SECOND, THIRD，因此最好完全松开LIKE并执行

。

SELECT TOTAL AS firstterm 
FROM studentmark
   JOIN subject ON subject.code=studentmark.code
WHERE studentmark.student_id='$name' 
  AND studentmark.YEAR='$ya' 
  AND studentmark.TERM = 'FIRST'");