New database connection problem

hi how do i rewrite this

$country_result = $conn->query('select * from sports');

and this

$bids_result = $conn->query('select * from bids where willwin='.$willwin.'');
$options = "<option value=''>Select Name</option>";
while($row = $bids_result->fetch_assoc()) {
$options .= "<option value='".$row['id']."'>".$row['bidder']."</option>";

to work like this

  $query = "SELECT a.id, a.team1, a.team2, b.auction, b.bidder, b.tagged, b.willwin, b.willlose FROM " . $DBPrefix . "auctions a
        LEFT JOIN " . $DBPrefix . "bids b ON (b.auction = a.id)
        WHERE a.id = :auc_id and a.id = a.id group by a.id";
        $params = array();
        $params[] = array(':auc_id', $id, 'int');
        $db->query($query, $params);
  thanks
1 Like

The last bit of code is a prepared statement. It calls the query using an array of parameters. The first query that you want to change doesn’t need to be a prepared statement as it has no parameters. The second one has a parameter and you could change it to a prepared statement by adding the parameter, the params array as in your second code.

But I’m not sure I’ve understood what you mean by “change this … to work like this”. Can you expand on that?

1 Like

yeah basically i want this code

require_once('conn.php');
$country_result = $conn->query("SELECT b.auction, b.bidder, b.tagged, b.willwin, b.willlose, s.team_id, s.teams FROM " . $DBPrefix . "vs_sports s LEFT JOIN " . $DBPrefix . "vs_bids b ON (b.id = s.team_id )
");




?>


<select name="country" id="vs_sports-list" onchange="changeSelect( this.value )">
<option value="1">Select Team</option>
<?php
if ($country_result->num_rows > 0) {
// output data of each row
while($row = $country_result->fetch_assoc()) {
?>
<option value="<?php echo $row["team_id"]; ?>"><?php echo $row["teams"]; ?></option>
<?php
}
}
?>
</select>
</br></br></br><div id="subcats">
<select name="bidder" id="vs_bids-list">
<option value='1'>Select Number</option>

</select></div>
<?php

$willlose = mysql_real_escape_string($_POST['willlose']);
if($willlose!='')
{
$vs_bids_result = $conn->query('select b.auction, b.bidder, b.tagged, b.willwin, b.willlose from vs_bids b where willlose='.$willlose.' ');
$options = "<option value=''>Select Name</option>";
while($row = $vs_bids_result->fetch_assoc()) {
$options .= "<option value='".$row['id']."'>".$row['bidder']."</option>";
}
echo $options;
}

?>

which can only connect to this:banghead:

<?php
$conn = new mysqli('localhost', 'root', 'pass', 'db');
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>

be able to connect to this

<?php
$DbHost     = "localhost";
$DbDatabase = "db";
$DbUser     = "root";
$DbPassword = "pass";
$DBPrefix    = "vs_";
$main_path = "D:\\xampp\\htdocs\\vsbet\\";
$MD5_PREFIX = "ca801ec4f5c664bc6a492f942b3a07eb";
?>

when rewritten like this

$query = "SELECT a.id, a.team1, a.team2, b.auction, b.bidder, b.tagged, b.willwin, b.willlose FROM " . $DBPrefix . "auctions a
        LEFT JOIN " . $DBPrefix . "bids b ON (b.auction = a.id)
        WHERE a.id = :auc_id and a.id = a.id group by a.id";
        $params = array();
        $params[] = array(':auc_id', $id, 'int');
        $db->query($query, $params);

cause it much more easier for me this way

hope u understand,? thanks

I’m not really seeing that much of a difference. You need to find the code that your last example uses to connect to the database, and use that instead of the other way. Other than that, things should be relatively straightforward, if not exactly the same.

yeah it connect now alright but not displaying the proper datas cause it get it datas from another table n displays and all content is displayed instead of what is being viewed at that moment, i will upload tables and images so u see what am talking about, n thanks for ur replies u been helpful

I don’t quite understand this part. Why are you creating an empty array and then immediately right after that, create a multidimensional array?

Use the variables you created in that file. That’s all.

I think it’s been copied from some other code that used more than one parameter, hence an array of arrays.

1 Like

Aren’t named parameters only exclusive for PDO? It makes no sense to be doing it this way and then also use mysqli_* connections along with mix matching the now removed mysql_* functions. Another thing is that it should also be using prepared statements since OP is attempting to use those parameters with the query. Really no point to make it more difficult than it really is.

thats y in my first comment i asked how i can convert it to make it much easier for me

So are you planning on using mysqli_* or PDO?

picture included, ok so they are basically 3 tables here

team data is pull from sports

table1 (sports) columns team_id, teams.

and on the page it shows MENU populated by teams name gotten from (sports)

Table 2 (bids) column id, auction, willwin, willlose, bidder
this is where the datas are all saved and thats will the submenu populate from the (willwin and willlose)

Table 3 is the (actions) now normally this is where i get the team names to populate the menu without the submenu using these script below

this code i was using with just menu code

// team select
        $query = "SELECT a.id, a.team1, a.team2, b.auction, b.bidder, b.tagged, b.willwin, b.willlose FROM " . $DBPrefix . "auctions a
        LEFT JOIN " . $DBPrefix . "bids b ON (b.auction = a.id)
        WHERE a.id = :auc_id and a.id = a.id group by a.id";
        $params = array();
        $params[] = array(':auc_id', $id, 'int');
        $db->query($query, $params);

        $TPL_team_list = '<select name="willwin" class="form-control">' . "\n";
        while ($row = $db->fetch())
        {
            $TPL_team_list .= "\t" . '
            <option value="' . $row[''] . '" ' . $selected . '>' . $row[''] . '</option>
            <option class="team1" value="' . $row['team1'] . '" ' . $selected . '>' . $row['team1'] . '</option>
            <option class="team2" value="' . $row['team2'] . '" ' . $selected . '>' . $row['team2'] . '</option>
            ' . "\n";
        }
        
        $TPL_team_list .= '</select>' . "\n";

{
    $template->assign_block_vars('tag_bidder', array(
    'TEAM' => $TPL_team_list,

            ));
    $i++;
}

but now am using this cause of it sub menu in it

<?php

ini_set('display_errors',0);
require_once('conn.php');
$country_result = $conn->query("SELECT b.bidder, b.tagged, b.willwin, b.willlose, s.team_id, s.teams FROM " . $DBPrefix . "vs_sports s 
LEFT JOIN " . $DBPrefix . "vs_bids b ON (b.willwin = s.team_id) 
LEFT JOIN " . $DBPrefix . "vs_auctions a ON (a.id = b.auction) 
WHERE a.id = b.auction");



?>


<select name="country" id="vs_sports-list" onchange="changeSelect( this.value )">
<option value="1">Select Team</option>
<?php
if ($country_result->num_rows > 0) {
// output data of each row
while($row = $country_result->fetch_assoc()) {
?>
<option value="<?php echo $row["team_id"]; ?>"><?php echo $row["teams"]; ?></option>
<?php
}
}
?>
</select>
</br></br></br><div id="subcats">
<select name="bidder" id="vs_bids-list">
<option value='1'>Select Number</option>

</select></div>
<?php

$willlose = mysql_real_escape_string($_POST['willlose']);
if($willlose!='')
{
$vs_bids_result = $conn->query('select b.auction, b.bidder, b.tagged, b.willwin, b.willlose from vs_bids b where willlose='.$willlose.' ');
$options = "<option value=''>Select Name</option>";
while($row = $vs_bids_result->fetch_assoc()) {
$options .= "<option value='".$row['id']."'>".$row['bidder']."</option>";
}
echo $options;
}

?>

<script src="js/dropdown/jquery-1.3.0.min.js"></script>

<script>
$('#vs_sports-list').on('change', function(){
var willlose = this.value;
$.ajax({
type: "POST",
data:'willlose='+willlose,
success: function(result){
$("#vs_bids-list").html(result);
}
});
});

</script>

now i get the full list of whats in bids table instead of just the two teams being viewed at the moment, with the previous codes it was much more easier

i hope u understand if not plz say u dont say i can break it down again, thanks

with the old script

it suppose to pick the teams from auctions team1 n team2

WHERE a.id = :auc_id
$params = array(‘:auc_id’, $id, ‘int’);

those code help display only item being viewed at the moment and not all the datas in the bids table

pdo …

Ok, have you looked at using PDO? If you haven’t, I’d suggest you read up on it.

am posting my previous code i want to try n work on that again i just created a submenu but not linked

this code i was working is interfering with my already existing codes

I can’t see how it would be working with all those different database syntax mixed with one another. I suggest turning on errors and have error logging turned on.

Also, I can’t help notice that you are using ->query incorrectly as well.

ok so after checking em am finally getting close i have them connected to one database and converted them from conn to db, the menu works fine but the sub menu disappears when i select a menu, the code below plz chk for me what am doing wrong thanks, i might not have converted all or missing a code or something

$query = "SELECT a.id, a.team1, a.team2, b.auction, b.bidder, b.tagged, b.willwin FROM " . $DBPrefix . "auctions a
        LEFT JOIN " . $DBPrefix . "bids b ON (b.auction = a.id)
        WHERE a.id = :auc_id and a.id = a.id group by a.id";
        $params = array();
        $params[] = array(':auc_id', $id, 'int');
        $db->query($query, $params);

?>


<select name="country" id="sports-list" onchange="changeSelect( this.value )">
<option value="1">Select Team</option>
<?php
if ($db->numrows() > 0) {
// output data of each row
while ($row = $db->fetch()) {
?>
<option value="<?php echo $row["willwin"]; ?>"><?php echo $row["team1"]; ?></option>
<option value="<?php echo $row["willlose"]; ?>"><?php echo $row["team2"]; ?></option>
<?php
}
}
?>
</select>
</br></br></br><div id="subcats">
<select name="bidder" id="bids-list">
<option value='1'>Select Number</option>

</select></div>
<?php
$willlose = mysqli_real_escape_string($_POST['willlose']);
if($willlose!='')
{


$query = "SELECT b.*, u.nick, u.rate_sum FROM " . $DBPrefix . "bids b 
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
WHERE willlose=".$willlose." and b.bidder NOT IN ('b.tagged') and b.tagged IN ('b.bidder') and b.auction = :auc_id order by b.willwin";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$db->query($query, $params);


$options = "<option value=''>Select Name</option>";
while ($row = $db->fetch()) {
$options .= "<option value='".$row['auction']."'>".$row['nick']."</option>";
}
echo $options;
}

?>

<script src="js/dropdown/jquery-1.3.0.min.js"></script>

<script>
$('#sports-list').on('change', function(){
var willlose = this.value;
$.ajax({
type: "POST",
data:'willlose='+willlose,
success: function(result){
$("#bids-list").html(result);
}
});
});

</script>

I don’t see a url parameter in your Ajax call, to tell the code what php code to call when the menu changes.