Using result of one MySQL query as variable in another MySQL query

Question

I'm trying to show the result HTML table based on team name

I'm able to echo right team name but unable to use it into a variable in my 2nd query I'm not able to find out what I'm doing wrong here why the query result is not visible. do I need to change something in my code?

<?php
include_once("connection.php");

$sql = "SELECT TeamName FROM `superuser` WHERE id = '303016'";
$queryRecords2 = mysqli_query($conn, $sql) or die("error to fetch employees data");

while ($row2 = $queryRecords2->fetch_assoc()) {
    echo $row2['TeamName']."<br>";
}

if(isset($_POST['search']))
{
    $valueToSearch = $row2['TeamName'];
    $valueToSearch2 = $_POST['valueToSearch2'];
    $valueToSearch3 = $_POST['valueToSearch3'];
    $sql = "SELECT * FROM `dailydata` WHERE TeamName = '".$valueToSearch."' and Date BETWEEN '".$valueToSearch2."' AND '".$valueToSearch3."'";
    $queryRecords = mysqli_query($conn, $sql) or die("error to fetch employees data");
}
else {
    $sql = "SELECT * FROM `dailydata` WHERE TeamName = ''";
    $queryRecords = mysqli_query($conn, $sql) or die("error to fetch employees data");
}

?>

Your best bet appears to be a [`JOIN`](https://dev.mysql.com/doc/refman/5.7/en/join.html) You should also use [Prepared Statements](https://secure.php.net/manual/en/mysqli.quickstart.prepared-statements.php) to avoid SQL Injection Attacks. `SELECT d.* FROM dailydata AS d INNER JOIN superuser AS s ON s.TeamName = d.TeamName WHERE d.Date BETWEEN ? AND ? AND d.TeamName = ?`. This will limit the results of `dailydata` to those that only exist in the superuser table — Will B., Jan 19 '19 at 15:03
**Warning:** You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](http://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). — Dharman, Jan 19 '19 at 15:05
_"the query result is not visible"_ - I don't see you outputting anything from the second query. Also, in your `else`-block, do you actually have a TeamName that's an empty string? `WHERE TeamName = ''`. If not, that query won't return any results at all and could be completely removed. — M. Eriksson, Jan 19 '19 at 15:05
I'm out puting the result in html table with and Yes i have TeamName that's an empty string. — yatendra singh ranawat, Jan 19 '19 at 15:09
You need to show us full code which relates to your problem. If you say you output doesn't work and you don't show us this code, we cannot help you. — Dharman, Jan 19 '19 at 15:10
Ok so i just posted the PHP MYSQL Script as the code i have for index page is big but i have put it on Google Doc for you guys to review https://docs.google.com/document/d/1nh_rPSS-aDX9u1oIjfeZcEZa2z2oQYWrMW7rAL0UEkM/edit?usp=sharing — yatendra singh ranawat, Jan 19 '19 at 15:14
You should show us all the _relevant_ code. If the page is too big, there's a lot of code that's not relevant that you can remove. If you post it as third party links, the question will be useless for future visitors when that link changes/gets deleted. Please read: [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). — M. Eriksson, Jan 19 '19 at 15:38
Possible duplicate of [PHP MySQL Query Where x = $variable](https://stackoverflow.com/questions/15703608/php-mysql-query-where-x-variable) — it4Astuces, Jan 19 '19 at 15:47

score 0 · Accepted Answer · 2019-01-19T16:08:29.760

0

For security purposes you should use prepared statements or even better you should use PDO instead of mysqli.

to fix this issue simply assign the value of $valueToSearch variable inside the loop.

<?php
include_once("connection.php");

$sql = "SELECT TeamName FROM `superuser` WHERE id = '303016'";
$queryRecords2 = mysqli_query($conn, $sql) or die("error to fetch employees data");

$valueToSearch;
while ($row2 = $queryRecords2->fetch_assoc()) {
    $valueToSearch = $row2['TeamName'];
}

if(isset($_POST['search']))
{
    $valueToSearch2 = $_POST['valueToSearch2'];
    $valueToSearch3 = $_POST['valueToSearch3'];
    $sql = "SELECT * FROM `dailydata` WHERE TeamName = '".$valueToSearch."' and Date BETWEEN '".$valueToSearch2."' AND '".$valueToSearch3."'";
    $queryRecords = mysqli_query($conn, $sql) or die("error to fetch employees data");
}
else {
    $sql = "SELECT * FROM `dailydata` WHERE TeamName = ''";
    $queryRecords = mysqli_query($conn, $sql) or die("error to fetch employees data");
}

?>

edited Jan 19 '19 at 16:08

answered Jan 19 '19 at 15:21

This Works like a charm Thank you so much – yatendra singh ranawat Jan 19 '19 at 15:28
1

Would you mind explaining what is the solution you offered? – Dharman Jan 19 '19 at 15:32
A good answer includes a proper explanation what you've changed and how it solved the question. You should also note that this code is _highly insecure_ since it's using unescaped user data directly in the query. – M. Eriksson Jan 19 '19 at 15:40
sorry for not providing an explanation, your query is correct, i just assigned the value for the $valuetosearch inside the loop. the $row2 is only exists inside the loop. since you're expecting only one result from the first query you can remove the while loop and directly assign $row2 = $queryRecords2->fetch_assoc() then you can use $valueToSearch = $row2['TeamName']; inside the if block – Jan 19 '19 at 15:49
@magnus-eriksson i know the code is insecure i didn't change anything i just fixed the issue, it would be much safer to use PDO and prepared statements – Jan 19 '19 at 15:57
1

You should either fix that in your answer (preferably) or at least make a note of it in the answer (which is fair enough). If future visitors see this answer, not knowing about the security issues, they will simply copy/paste it into their own code, thinking it's best practice/secure enough since it was accepted on SO. That's why it's important to note those things in the answer itself. – M. Eriksson Jan 19 '19 at 16:00
I'm still new here, i will edit the answer right now ,thanks for the advice – Jan 19 '19 at 16:02
`PDO` and `mysqli` each have their [pros and cons](https://secure.php.net/manual/en/mysqli.overview.php#mysqli.overview.mysqlnd). For example PDO supports client-side prepared statements and named parameters`(:placeholder)` that mysqli does not, Where mysqli supports all MySQL 4.1+ functionality and [asynchronous queries](https://secure.php.net/manual/en/mysqli.reap-async-query.php) that PDO does not. Main point is, you should use the database API that satisfies your application development requirements. – Will B. Jan 19 '19 at 18:59

Using result of one MySQL query as variable in another MySQL query

1 Answers1