Please help with PHP (getting value of a string and querying database)

Discussion in 'Programming' started by alex06295, Jul 8, 2016.

  1. #1
    <?php
    $servername = "localhost";
    $username = "xxx";
    $password = "xxx";
    $dbname = "xxx";
    $productBrand = "$productBrand";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }

    $sql = "SELECT * FROM mytable WHERE title LIKE '%$productBrand%' limit 5";


    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {

    echo'<p>'. $row['title'].'</p>';
    echo'<p>'. $row['id'].'</p>';
    echo'<p>'. $row['discount'].'</p>';


    }
    } else {
    echo "0 results";
    }
    $conn->close();
    ?>


    But it is not translating the value of $productBrand, how to do it correctly?
     
    alex06295, Jul 8, 2016 IP
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    First off, you need to secure that query. Putting a non-verified user-input directly into a query is asking for trouble. And, where do you get that variable from? You're assigning $productBrand = "$productBrand" - that makes no sense - you're assigning an existing variable to the same variable name.

    However, forgetting about that, the query should be like this:
    (and, in the future, make sure you use the [ code ] or [ php ] code to post code (wrap the code in those, just remove the spaces)

    
    /* create a prepared statement */
    if ($stmt = "SELECT * FROM mytable WHERE title LIKE ? LIMIT 5";)) {
    /* bind parameters for markers */
    $stmt->bind_param("s", '%'.$productBrand.'%');
    /* execute query */
    $stmt->execute();
    /* bind result variables */
    $stmt->bind_result($district);
    /* fetch value */
    
    while($row = $stmt->fetch_assoc()) {
    echo'<p>'. $row['title'].'</p>
    <p>'. $row['id'].'</p>
    <p>'. $row['discount'].'</p>';
    }
    /* close statement */
    $stmt->close();
    }
    
    PHP:
    This is taken from an example at php.net, and modified quickly with your code, not tested, might not work as intended - it should, however, give you an idea.

    I don't use mysqli, so I'm a little bit out of my depth on how it works, so some things that I pasted MIGHT not work, but it should, in theory work
     
    PoPSiCLe, Jul 8, 2016 IP
  3. alex06295

    alex06295 Well-Known Member

    Messages:
    944
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    130
    #3
    But it is not working, even I put the value like panasonic (that is available in the productbrand column in my database) instead of $productbrand
     
    alex06295, Jul 8, 2016 IP
  4. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #4
    Well. How is it not working? Do you get errors? Does it just not show any results? Check the logs, turn on error-display, so you can see warnings, notices and errors, and so on.

    Btw, how is the value stored in the database? Is it stored like "panasonic", or is it stored like "Panasonic" - searches in SQL isn't by default case-insensitive
     
    PoPSiCLe, Jul 8, 2016 IP
  5. alex06295

    alex06295 Well-Known Member

    Messages:
    944
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    130
    #5
    I think, it is data-type issue in my mysql table

    what data-type should be there? I have

    Some columns have TEXT, CHAR, VARCHAR
     
    alex06295, Jul 9, 2016 IP
  6. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #6
    Data type has nothing to do with it. You can search any data type.
     
    PoPSiCLe, Jul 9, 2016 IP
  7. alex06295

    alex06295 Well-Known Member

    Messages:
    944
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    130
    #7
    If

    $abc = panasonic

    and I use $abc as query, it works for the value of panasonic.

    But title is not working like $title or productbrand not working like $productbrand

    why?
     
    alex06295, Jul 9, 2016 IP
  8. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #8
    I'm not 100% sure what you're saying...

    You might want to rephrase the question, or put some quick code-examples. Because it's a bit hard to follow.

    $abc = panasonic won't work for anything, since panasonic isn't contained. So I'm guessing you mean $abc = 'panasonic' or $abc = "panasonic"

    When it comes to your next sentence, about what doesn't work, there's no difference between

    $abc = 'panasonic';
    or
    $title = 'panasonic';
    or
    $productbrand = 'panasonic';

    If the latter doesn't work, you might wanna take a look at what is actually put into those variables. Ie, is the value you assign to those variables empty? Is there something else, somewhere, that creates problems?
     
    PoPSiCLe, Jul 9, 2016 IP
  9. alex06295

    alex06295 Well-Known Member

    Messages:
    944
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    130
    #9
    I was saying that if I define

    $abc = "panasonic";

    and use like variable for example:

    $strSQL = "SELECT * FROM aircon WHERE title like '%$abc%'";

    it works.

    But when it comes to getting the value from database, and then using that value, don't work.

    Is there any way to get just one value of single field and then use it as string?
     
    alex06295, Jul 10, 2016 IP
  10. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #10
    Of course it is - and if the name of the column in the table in the database, and you use it like you stated above, (like $row['title']) it should work.
    
    $servername = "localhost";
    $username = "xxx";
    $password = "xxx";
    $dbname = "xxx";
    $productBrand = "$productBrand";
    
    $conn = new mysqli($servername, $username, $password, $dbname);
    
    if ($conn->connect_error) {
       die("Connection failed: " . $conn->connect_error);
    }
    
    $stmt = $conn->query("SELECT * FROM mytable WHERE title LIKE ? LIMIT 5");
    $stmt->bind_param("s", '%'.$productBrand.'%');
    $stmt->execute();
    
    while   ($row = $stmt->fetch_assoc()) {
       echo'<p>'. $row['title'].'</p>
       <p>'. $row['id'].'</p>
       <p>'. $row['discount'].'</p>';
    }
    
    PHP:
    That should work. However, you need to make sure that there is actually content in the $productBrand-variable.

    I'm sorry, but what you're asking is basic database-handling - fetching data from a database is very basic, and if you don't get the result you expect, then you're doing something wrong, which is why you should turn on displaying PHP-errors, and make sure that you have an error-handler for the database connection
     
    PoPSiCLe, Jul 10, 2016 IP
  11. alex06295

    alex06295 Well-Known Member

    Messages:
    944
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    130
    #11
    This problem is also solved:

    $pb = $row['productbrand'];
    $kw = $row['keyword'];

    $strSQL = "SELECT * FROM aircon WHERE id = $abc";
    $strSQL = "SELECT * FROM aircon WHERE title like '%kw%' or title like '%$pb%' limit 5";


    $rs = mysql_query($strSQL);
    // Loop the recordset $rs
    while($row = mysql_fetch_array($rs)) {

    echo'<h1>'. $row['title'].'</h1>';


    My next problem is related to making url seo friendly........

    I want to change my current url - mysite.com/product.php?id=5

    to

    mysite.com/product.php?id=5&title=a-product-name

    and trim '&title=' and leave id=5 as it is because it is referenced to get id to fetch data........ for example

    $abc = $_GET['id'];

    and

    $strSQL = "SELECT * FROM aircon WHERE id = $abc";

    Is there any way to do it with my .htaccess file?
     
    alex06295, Jul 12, 2016 IP