0

In my PHP I make a sql query to my database that is a list of teams. On some teams the query returns null. If I make the same query in phpmyadmin it returns the value I seeking for.

My query code:

$sql = $mysqli->query("
    SELECT `team`
    FROM `dota teams`
    WHERE `team` LIKE '%$team%'
    OR `teamalt` LIKE '%$team%'
")  or die($mysqli->error);

For example if the query from my website is te it returns null, but if the query is titan it returns Titan Esports. And with Virtus Pro it return the value i want if i use vp or virtus. With NAVI i return null either what. Evil Geniuses also return value either if i write EG or Evil.

How come it can that with some name i get a returned value and with some not?

enter image description here

Full PHP code:

$date = $mysqli->real_escape_string(date("d\-m\-Y"));

$team = set_space($team); //Set spaces in teams so we can ute it for search

// Get team name
$sql = $mysqli->query("
    SELECT `team`
    FROM `dota teams`
    WHERE `team` LIKE '%$team%'
    OR `teamalt` LIKE '%$team%'
")  or die($mysqli->error);

if($sql->num_rows>0){
    $team = $sql->fetch_array();
    $team = $team['team'];

// Get all the matches
    $sql = $mysqli->query("
        SELECT * FROM `dota schedule`
        WHERE `date` >= '$date' AND `teams`
        LIKE '%$team%' ORDER BY `date`,`time` ASC
    ")  or die($mysqli->error);

    // Loop through all the results
    while ($data = $sql->fetch_array()){
        // Change the time based on timezone
        $time_arr = str_split($data['time'],3);
        $hour = $time_arr[0] + $time_add;
        $min = str_replace(":","",$time_arr[1]);
        $time = "$hour : $min";

        // Get teams
        $teams = get_teams($data['teams']);

        // Get casters
        $caster = explode('_', $data['caster']);
        foreach($caster as &$c){
            $c = get_string_between($c,'[',']');
        }
        // Loop through to see which language the casters has.
        foreach($caster as &$c){
            $sqls = $mysqli->query("SELECT `language` , `stream` FROM `dota casters` WHERE `name` = '$c'") or die($mysqli->error);
            $da = $sqls->fetch_array();
            if($da['language'] == "English"){
                $stream = $da['stream'];
                $c = "<a href='$stream'><img src='http://joffe.kottnet.eu/flags/uk.png' alt='English'>$c</a>";
            }
            else if($da['language'] == "Russia"){
                $stream = $da['stream'];
                $c = "<a href='$stream'><img src='http://joffe.kottnet.eu/flags/russia.png' alt='English'>$c</a>";
            }
        }
    ?>
        // Write out table.
        <tr>
            <td><?= "Date:" . $data['date'] . " Time: " .$time?></td>
            <td><?= $data['cup'] ?></td>
            <!-- 3 TD for teams -->
            <td class="team1"><?= $teams[0] ?></td>
            <td class="vs">VS</td>
            <td><?= $teams[1] ?></td>
            <td><?= print_out_array($caster); ?></td>
        </tr>
    <?php
    }
}
Cœur
  • 37,241
  • 25
  • 195
  • 267
Olof
  • 776
  • 2
  • 14
  • 33

2 Answers2

0

For the question, "How come it can that with some name i get a returned value and with some not?", it's because your query is selecting only one field, team, but your where clause is looking at two fields, with or logic.

Therefore if you have a record where the team field is null and the teamalt field has a value of 'te', then a query with 'te' as the search parameter will return one row with a null value.

What to do about it depends on your requirements. How do do it depends on your database engine which does not appear to be specified.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • I don't really think that the `teamalt` not being in the `SELECT` could be the reason. You can do `SELECT id FROM t WHERE name = "blablabla"`(example) without a problem. – Minoru Oct 21 '13 at 12:29
  • I use phpMyAdmin 3.5.7 atm. If I understand you answer right the query returns 2 rows? One that is null and one with the return i want? If so just I just could check if first row is null if so i check the second row. – Olof Oct 21 '13 at 12:29
  • Complementing the first comment: The reason is that the `WHERE` clause is evaluated first than the `SELECT` clause (http://stackoverflow.com/questions/2842262/oracle-sql-clause-evaluation-order). – Minoru Oct 21 '13 at 12:35
0

Maybe your problem is on the

$team = set_space($team);

I'm not sure, since you didn't post from where the $team comes from. You can check if your query is well written by making you PHP code to echo the query. Comment everything after the echo, so you won't get any error.

If you query is wrong, try to change it for:

$sql = $mysqli->query("
    SELECT `team` 
    FROM `dota teams` 
    WHERE `team` LIKE '%" . $team . "%' 
    OR `teamalt` LIKE '%" . $team . "%'
")  or die($mysqli->error);.
Minoru
  • 1,680
  • 3
  • 20
  • 44
  • Sry about i got to include the where i got the team from. I get it from a get method and if the team name has any spaces like 4 friends + chrille. To put the name in a get i simply just did 4_friends_+_chrille and then i made a method to get back the spaces. – Olof Oct 21 '13 at 12:50
  • @Olof Your query was null? Or your `$team` was null? If you use an `` to submit the name, you don't really need to put the underscore. – Minoru Oct 21 '13 at 12:54
  • The query returned zero rows. Team has a value, I have even tested that with script. – Olof Oct 21 '13 at 12:56
  • You have to check if the value of `$team` is the same value that you put on it. This is the only problem that may occur now. Have you tested the query I made? – Minoru Oct 21 '13 at 12:59
  • Yes i have tester your query you made and i have testet directly in the code to write $sql = $mysqli->query(" SELECT `team` FROM `dota teams` WHERE `team` LIKE 'te' OR `teamalt` LIKE 'te' ") or die($mysqli->error);. – Olof Oct 21 '13 at 13:43