PHP/MySQL picky about data selecting

Question

Ive ran into a bit of a problem. Im trying to pull data from a column in which values in rows are the same. Ive use SELECT * FROM teams where number=$number Where $number is specified by the user. It works on some rows,but some not. Its very weird.

http://pastebin.com/4CK3mmpd

<link rel='icon' href='/image/database_accepted.png' />
<body style="background-color:black">
<?php
   include 'includes/connect.php';
    //Include the login varibles

    //Setup a query for all teams into a varible

    $number_select = $_GET['team'];

    $query = "SELECT * FROM teams WHERE number='$number_select'";
    $result = mysql_query($query);
    $test = mysql_fetch_array($result);
     if($test==null){
        echo "<img src='/image/no_data_found_paramaters.png'>";
    }else{
        }
?>

<style>
table, th, td {
    border: 1px solid black;
}
</style>
<?php
echo "<table bgcolor='#00FF00'>";
echo "<tr>";
echo "<th>Team Name</th>";
echo "<th>Team Number</th>";
echo "</tr>";
echo "<font color='red'>";
    while($team = mysql_fetch_array($result)) {
echo  "<tr>";
echo    "<td>" . $team['Name'] . "</td>";
echo    "<td>" . $team['Number'] . "</td>";
echo   "</tr>";


    }
?>

EDIT: Requested operation paste:

CREATE TABLE `teams` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(25) NOT NULL,
  `Number` int(4) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Drew, Sep 19 '15 at 00:51
@Drew, probably. this isnt a corporate website, Juts a HS robotics scouting DB. — Jigsaw Robotics, Sep 19 '15 at 00:54
please run `show create table teams` and paste results under [edit] — Drew, Sep 19 '15 at 00:54
@Drew will do,and I do appreciate the info. Will look into it — Jigsaw Robotics, Sep 19 '15 at 00:56

score 0 · Accepted Answer · edited May 23 '17 at 11:44

Change over to a safe library like mysqli or pdo, and use them in proper ways. See this wildly popular link.

Not that that was your problem. You were never getting your first row due to calling fetch twice (the first time outside of the while).

So if you had no duplicates for a number, then the only row to fetch was already fetched and never made it into the while loop. In the schema below, number 9 would only show 2, not 3, rows in the html table. You get the idea.

Try this:

<link rel='icon' href='/image/database_accepted.png' />
<body style="background-color:black">
<?php
   include 'includes/connect.php';
    //Include the login varibles

    //Setup a query for all teams into a varible

    $number_select = $_GET['team'];

    $query = "SELECT * FROM teams WHERE number='$number_select'";
    $result = mysql_query($query);
    //$test = mysql_fetch_array($result);
    // if($test==null){
    //    echo "<img src='/image/no_data_found_paramaters.png'>";
    //}else{
    //    }
?>

<style>
table, th, td {
    border: 1px solid black;
}
</style>
<?php
echo "<table bgcolor='#00FF00'>";
echo "<tr>";
echo "<th>Team Name</th>";
echo "<th>Team Number</th>";
echo "</tr>";
echo "<font color='red'>";
    while($team = mysql_fetch_array($result)) {
echo  "<tr>";
echo    "<td>" . $team['Name'] . "</td>";
echo    "<td>" . $team['Number'] . "</td>";
echo   "</tr>";
    }
?>

Easily seen if someone mimics your data with this

Schema

CREATE TABLE `teams` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(25) NOT NULL,
  `Number` int(4) NOT NULL,
  PRIMARY KEY (`ID`)
)

insert teams (name,number) values ('aaaa',9);
insert teams (name,number) values ('aaaa',9);
insert teams (name,number) values ('zzzz',9);

insert teams (name,number) values ('blah',10);

PHP/MySQL picky about data selecting

1 Answers1

Schema