Trying to use SELECT, WHERE and LIKE with a variable doesn't work

51
June 21, 2018, at 06:30 AM

thanks for reading.

I have two tables, one called 'pagetable' and one called 'tablestream'.

Within my PHP, I have this code.

<?php
  $sqlout1f = "SELECT source FROM pagetable WHERE output LIKE 'out1f'";
  $resultout1f = mysqli_query($conn, $sqlout1f);
  $sqlout1fsrc ="SELECT streamurl FROM tablestream WHERE vanrole LIKE '{resultout1f}'";
  $resultsqlout1fsrc = mysqli_query ($conn,$sqlout1fsrc);
...
while($row = mysqli_fetch_array($resultsqlout1fsrc)) {
echo $row['streamurl'];
}
?>

I don't get any errors at all, but also don't get anything at all on the page. I have tried replacing the variable with the string that the variable has stored and it works fine

EDIT: Once I've put the '$' in before the variable that I forgot, I get this:

Recoverable fatal error: Object of class mysqli_result could not be converted to string in C:\xampp\htdocs\newpage.php on line 22

Line 22 is: $sqlout1fsrc ="SELECT streamurl FROM tablestream WHERE vanrole LIKE '{$resultout1f}'";

EDIT 2: out1f is text, not a variable, the variable in question is {$resultout1f}

Answer 1

There is one burning issue in this, and that is the following code;

$sqlout1f = "SELECT source FROM pagetable WHERE output LIKE 'out1f'";
$resultout1f = mysqli_query($conn, $sqlout1f);
$sqlout1fsrc ="SELECT streamurl FROM tablestream WHERE vanrole LIKE '{resultout1f}'";
$resultsqlout1fsrc = mysqli_query ($conn,$sqlout1fsrc);

If you add the $ to the second of the queries, where you use the variable, you are using a resource, not a string, this is because $resultout1f is the result from the first query, i.e. a dataset, not a data result, you need to get the information out of the result set before you can use any of it in a secondary query

Answer 2

You are using the LIKE clause in the Wrong manner. Here is the basics of LIKE clause:

LIKE is normally used in 3 ways :

1.) LIKE '%[STRING]%' - This specifies that your [STRING] can be at any position.

2.) LIKE '[STRING]%' - This Specifies the condition that it should start with your [STRING]

3.) LIKE '%[STRING]' - This Specifies the condition that it should end with your [STRING]

Coming to your problem. You can modify your statements to use placeholders and later repplace the placeholders with the actual value like mentioned below:

$sqlout1f = "SELECT source FROM pagetable WHERE output LIKE '%out1f%'";
$sqlout1fsrc ="SELECT streamurl FROM tablestream WHERE vanrole LIKE '%[resultout1f]%'";
$resultout1f = mysqli_query($conn, $sqlout1f);

If your Row Count is equal to 1:

$row = mysqli_fetch_array($resultout1f, MYSQLI_ASSOC);
$sqlout1fsrc = str_replace('[resultout1f]', $row['source'], $sqlout1fsrc);
$resultsqlout1fsrc = mysqli_query ($conn,$sqlout1fsrc);
while($row = mysqli_fetch_array($resultsqlout1fsrc, MYSQLI_ASSOC)) {
    echo $row['streamurl'];
}

If your Row Count is Greater Than 1:

while($row = mysqli_fetch_array($resultout1f, MYSQLI_ASSOC)) {
    $sqlout2fsrc = $sqlout1fsrc;
    $sqlout2fsrc = str_replace('[resultout1f]', $row['source'], $sqlout2fsrc);
    $resultsqlout1fsrc = mysqli_query ($conn,$sqlout2fsrc);
    while($row2 = mysqli_fetch_array($resultsqlout1fsrc, MYSQLI_ASSOC);) {
        echo $row2['streamurl'];
    }
}

You can find more information on LIKE clause Here.

Hope this helps.

Answer 3

You should bind your query string with %% while using LIKE

SELECT * FROM TABLE WHERE COLUMN LIKE "%query%"

%a => anything that ends with a like santa, comma
a% => anything that starts with a like application, answer
%a%=> anything that has a in middle cat ,madam

READ ALSO
Can i create a background in the bottom another width and in the top another width

Can i create a background in the bottom another width and in the top another width

I have made a line with height 20px and width 100% but how can I achieve what it's in the photo this black line at the end it is like a slashSecond photo what I am trying to achieve and first it is my actual design

48
Line up list items in ionic and get a value

Line up list items in ionic and get a value

I have an ionic project that I'm working on and I'm using the columns in a grid that contains a static image on the top left (X) and a list of images on the right (Y)What I would like to do is be able to scroll through the list on the right so when the X and Y images...

64
Equal sized child Divs

Equal sized child Divs

I have a list of products which will be either 5 or 6 (depending on the relating product) I would like to always have the first 2 Products (views-row-1&2) on the first row, but then for the rest to fill the width of the second row

72
Bootstrap Glyphicon different colors on different machines

Bootstrap Glyphicon different colors on different machines

I have a column of glyphicon pushpins that appear black on my own machine (Windows 7) and appear red on another machine (Windows 10) Both are using Chrome to view the page

69