quering data from a table are missing

$items = '';
$sql = "select items from bplus where pos = 'home-01' limit 1";
$st = $db->query($sql);
$res = $st->fetchColumn();
$arr = explode("\n", $res);
foreach ($arr as $el){
//echo $el . "<br>"; // as a test - it's ok - four images.      
$sqlb = "select * from banners where fname = '" . $el . "'";
    $stb = $db->query($sqlb);
    $row = $stb->fetch();
    $items .= "<img class='itemtop' src = '../banners/" . $el . "' alt='img' data-id = " . $row['id'] . " data-fname = '" . $row['fname'] . "' data-w = " . $row['width'] . " data-h = " . $row['height'] . ">\n"; 
}
echo $items;

final result:

<img class='itemtop' src = '../banners/5ad461206f52e.jpg
' alt='img' data-id =  data-fname = '' data-w =  data-h = >
<img class='itemtop' src = '../banners/5ad461206f916.jpg
' alt='img' data-id =  data-fname = '' data-w =  data-h = >
<img class='itemtop' src = '../banners/5ad461206fcff.jpg
' alt='img' data-id =  data-fname = '' data-w =  data-h = >
<img class='itemtop' src = '../banners/5ad461207d7c2.jpg' alt='img' data-id = 29 data-fname = '5ad461207d7c2.jpg' data-w = 320 data-h = 50>

Why are the first three images without any data?
In the table all images has its data.

2 answers

  • answered 2018-04-17 05:31 ArtisticPhoenix

    You need to check the results of your inner query

    $sqlb = "select * from banners where fname = '" . $el . "'";
    $stb = $db->query($sqlb);
    $row = $stb->fetch();
    
    if(!$row) throw new Exception("missing row [$el]");
    
    $items .= "<img class='itemtop' src = '../banners/" . $el . "' alt='img' data-id = " . $row['id'] . " data-fname = '" . $row['fname'] . "' data-w = " . $row['width'] . " data-h = " . $row['height'] . ">\n"; 
    

    For the items I would add this (outside the loop)

    $createData = function($k, $v){
        return 'data-'.$k.'="'.$v.'"';
    };
    

    Then in the loop after checking row

    if(!$row) throw new Exception("missing row $el");
    
    $data = implode(' ',array_map($createData, array_keys($row), $row)); 
    
    $items .= '<img class="itemtop" src="../banners/'.$el.'" alt="img" '.$data.' >'.PHP_EOL; 
    

    This way it dynamically creates the data- based of what's in the query, then if you change "stuff" it is less code to maintain.

    You can test it here

    https://3v4l.org/Lo9Vf

    One last thing is you can rename the columns in the query if they don't match, for example say in the DB the w is actually width then you can rename it like this

    $sqlb = "select width AS w, first AS fname from banners ....;
    

    And so on, I mention it because latter if you add a column to the data you only need to change the query and your all set, you don't have to change how the HTML is built, but that is up to you. It's just how I would do it.

    UPDATE

    The bigger issue here is you Database model is not really correct, which gives rise to this issue an other complications.

    Basically you have a many to relationship between the two tables and you are severely limiting what you can do by not molding the data correctly. Currently you have

    //table bplus 
    items
    1,2,3,4
    
    //table banners
     fname
     1
     2
     3
     4
    

    Don't worry about the numbers the actual data in there is pretty much irrelevant.

    What you need is something like this

    //table bplus 
     //other fields
    
    //table banners
    fname bplus_id
    1       1
    2       1
    3       1
    4       1 
    

    Honestly I would need more information about the database to really help more. But I suggest looking into Many to One relationships. As you can eliminate continuously calling the DB inside the loop, which can get expensive, and probably simplify a lot of things.

  • answered 2018-04-17 05:31 B. Desai

    It looks like your $el variable contains spaces which cause the issue. So trim this data then get data from db

    $sqlb = "select * from banners where fname = '" . trim($el) . "'";
        $stb = $db->query($sqlb);
        $row = $stb->fetch();
        $items .= "<img class='itemtop' src = '../banners/" . $el . "' alt='img' data-id = " . $row['id'] . " data-fname = '" . $row['fname'] . "' data-w = " . $row['width'] . " data-h = " . $row['height'] . ">\n"; 
    }
    echo $items;
    

  • update query dosen't work
    SELECT codefard,ave,class,
          IF(ave=@_last_ave,@curRank:=@curRank,@curRank:=@_sequence) AS rank,
           @_sequence:=@_sequence+1,@_last_ave:=ave
        FROM grade, (SELECT @curRank := 1, @_sequence:=1, @_last_ave:=0) r
        ORDER BY  ave DESC
    

    this query is works correctly but when i update this answer into table doesn't work.

    UPDATE grade JOIN (SELECT codefard,ave,class,
       IF(ave=@_last_ave,@curRank:=@curRank,@curRank:=@_sequence) AS rank,
                  @_sequence:=@_sequence+1,@_last_ave:=ave
        FROM grade, (SELECT @curRank := 1, @_sequence:=1, @_last_ave:=0) r
        ORDER BY  ave DESC)as d ON grade.codefard = d.codefard SET grade.allrank=d.rank
    
  • Hibernate: could not extract ResultSet; SQL [n/a]

    I have that nativeQuery Query in my Repository.

        @Query(value = Constant.QUERRY_FIND_GUIDE, nativeQuery=true)
    List<Guide> findGuide(String locationName, String gender, String type, String language);
    

    and Constant.QUERRY_FIND_GUIDE is

    public static final String QUERRY_FIND_GUIDE = "SELECT g.id FROM Guide g " + 
            " LEFT JOIN g.user u  " + 
            " LEFT JOIN g.locations l " + 
            " LEFT JOIN g.languages lang "+
            "WHERE (UPPER(?1)=UPPER('NULL') OR ?1 IS NULL OR ?1 ='' OR l.locationName = ?1) "
            + " AND (UPPER(?2)=UPPER('NULL') OR ?2 IS NULL OR ?2 ='' OR g.gender =?2) "
            + " AND (?3 IS NULL OR ?3 ='' OR "
            + " (CASE WHEN UPPER(?3)=UPPER('NULL') THEN g.cardnumber IS NULL ELSE g.cardnumber IS NOT NULL END) )"
            + " AND (UPPER(?4)=UPPER('NULL') OR ?4 IS NULL OR ?4='' OR lang.language = ?4)";
    

    when i run the Page I have that error: 2018-04-24 03:05:32.198 WARN 1340 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1064, SQLState: 42000 2018-04-24 03:05:32.198 ERROR 1340 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (UPPER('Hà Nội')=UPPER('NULL') OR 'Hà Nội' IS NULL OR 'Hà Nội' ='' at line 1 2018-04-24 03:05:32.208 ERROR 1340 --- [nio-8080-exec-4] c.w.tgo.controller.OperatorController : error search: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet i try all solution on google but can't fix it. Can somehone hlp me please!!!!

  • Unhandled rejection SequelizeConnectionRefusedError: connect ECONNREFUSED

    I keep getting this error when I run my server with mysql and nodemon. I understand that part of the solution is that my database server is not running, but I do not know how to get it back up and running. I have my root, host, password, database etc., all set up, and the server use to work before, but I have no idea why it would not be working now. Can anyone help me?

    Thank you.

  • PHP setting session variables

    Been having this issue for a while, tried asking for solutions but still unable to find the answer I'm looking for. I'm trying to build a practice boxing website which will match the user logged into the session against others in the table. the issue I'm having is that I can put users weight (the program will match people based on weight) into a variable, but I cannot make a variable for the person who is logged into the session. the code I have so far is:

    <?php
    
    session_start();
    include "connection.php";
    
    $id = $_GET["userID"];
    $sql = "SELECT * FROM userdetails WHERE userID = '" . $id . "'";
    $result = mysqli_query($con, $sql);
    
    if (mysqli_num_rows($result) > 0) {
    ($row = mysqli_fetch_assoc($result)) {
        echo"<div>" .
        "<br>First name: " . $row["firstname"] .
        "<br>Second name: " . $row["secondname"] .
        "<br>Mobile: " . $row["mobile"] .
        "<br>Height: " . $row["height"] . "CM" .
        "<br>weight: " . $row["weight"] . "KG" .
        "<br>Image:<br> <img src=getimage.php? userID=" . $row["userID"] . 
        "width=100, height=100".
        "</div>";
    }
    } else {
    echo"0 results";
    }
    $weight = $row['weight'];
    ?>
    

    this code displays a user in the table, the $weight variable contains this users weight. What I need is a variable which contains can contain the weight of the user logged in. i.e. I log in as user A, and check to see if i match user B. I then log out and log in as user C, and check if i match user b. the variable I'm looking for here is the one which would contain user A and C's weight.

    I'd appreciate any help on the matter

  • How to submit a form generated from an ajax $.get?

    I have this page on document ready document ready

    Once a customer is selected from the customer select input an ajax $.get is performed onchange and displays the billing address form and associated values if any from the database customers

    jquery EXAMPLE PURPOSES ONLY

    $("#cus").change(function(){
       cusValue = $(this).val();
       $.get("info.php", {cusValue: cusValue}, function(data){
          $("#info").html(data);
       });
    });
    

    enter image description here

    HTML EXAMPLE PURPOSES ONLY

    <form action='".update($conn)."' method='post'>
      <select name='cus'><option value='".$cus."'>".$cus."</option></select>
      <input name='a1' type='text' value='".$a2."'>
      <input name='a2' type='text' value='".$a3."'>
       ...
      <input name='submit' type='submit' value='submit'>
    </form>
    

    PHP EXAMPLE PURPOSES ONLY

    function update($conn){
    if (isset($_POST['submit'])) {
        $cus = mysqli_real_escape_string($conn, $_POST['cus']);
        $a1 = mysqli_real_escape_string($conn, $_POST['a1']);
        $a2 = mysqli_real_escape_string($conn, $_POST['a2']);
        $a3 = mysqli_real_escape_string($conn, $_POST['a3']);
        $a4 = mysqli_real_escape_string($conn, $_POST['a4']);
        $a5 = mysqli_real_escape_string($conn, $_POST['a5']);
        $a6 = mysqli_real_escape_string($conn, $_POST['a6']);
    
        mysqli_query($conn,  "UPDATE customers SET a1 ='$a1', a2 ='$a2', a3 ='$a3', a4 ='$a4', a5 ='$a5', a6 ='$a6' WHERE customer = '$cus'");
            }
         }
    

    I do not have any syntax errors

    On a normal page form gets submitted and myqli table is updated. However when the form that has been generated into an ajaxed div is submitted myqli table does not get updated after submit button is clicked! Is there a way around this?

  • Why does my query, when executed in PHP, add an extra space?

    I have sat up a simple Curl to navigate to a page, and save the result in a string. The goal is to get a adress from the page (wrapped in a specific html tag), and then save that to a database on my server

    $url = "http://www.odensebolig.dk/property/vindegade-128-lejl-nr-115-5000-odense-c/";
    $agent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)';
    
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    curl_setopt($ch, CURLOPT_VERBOSE, true);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_USERAGENT, $agent);
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_REFERER, "http://www.google.dk");
    curl_setopt($ch, CURLOPT_HTTPHEADER, Array("Content-Type:application/x-www-form-urlencoded"));
    $result = curl_exec($ch);
    

    Then i find a adress between 2 html-tags and save that result in a variable. Example: "Vindegade 128, lejl. nr. 115, 5000 Odense C". I've found a function here on stackoverflow i use for this. Then i split the string into array with adress, zip, and town, and finaly make a query updating the adress in my adresses table:

    function get_string_between($string, $start, $end) {
        $string = ' ' . $string;
        $ini = strpos($string, $start);
        if ($ini == 0)
            return '';
        $ini += strlen($start);
        $len = strpos($string, $end, $ini) - $ini;
        return substr($string, $ini, $len);
    }
    
    //To get the full adress i do this
    $full_adress = get_string_between($result, '<h4>', '</h4>');
    //And the result:
    echo $full_adress; //Vindegade 128, lejl. nr. 115, 5000 Odense C
    
    //Find last comma
    $last_comma = strrpos($full_adress, ',');
    //Save all text before last comma
    //And here i have tried removing all kinds of line breaks, and trim trailing and endeing spaces
    $adress['adress'] = preg_replace("/\r|\n/", "", trim(substr($full_adress, 0, $last_comma)));
    $adress['zip'] = substr($full_adress, ($last_comma + 2), 4);
    $adress['town'] = substr($full_adress, ($last_comma + 7));
    //And the result of [adresse]:
    echo $adress['adress']; //Vindegade 128, lejl. nr. 115
    //I then put the info in a query
    $query = "UPDATE adresses SET `adress` = '". $adress['adress'] ."' WHERE `url` = '$url'";
    //Go!
    if($objCon->query($query)){
        echo 'Done';//Done
    }
    

    Now the strange thing is that in my adresses table, the record for the url is becoming: "Vindegade 128, lejl. nr. 115" (With a extra space between "Vindegade" and "128"). Why? If i instead echo out my query, and manually insert it into phpmyadmin, the record is not updated with the extra space