Member Avatar for david_105

Am able to use this code to bind a.id = :auc_id in order to display certain result needed for menu code, but when i introduce it in the submenu i dont get any result below are the codes for menu and submenu'

MENU works fine, display whats need to be showned thanks to a.id = :auc_id

$query = "SELECT s.team_id, s.teams AS teams1, ss.team_id, ss.teams AS teams2, a.id, a.team1, a.team2 FROM " . $DBPrefix . "auctions a
        LEFT JOIN " . $DBPrefix . "sports s ON (s.team_id = a.team1)
        LEFT JOIN " . $DBPrefix . "sports ss ON (ss.team_id = a.team2)
        WHERE a.id = :auc_id and a.id = a.id";
        $params = array();
        $params[] = array(':auc_id', $id, 'int');
        $db->query($query, $params);
?>

<script type="text/javascript" src="js/dropdownjquery.js"></script>
<script type="text/javascript">
$(document).ready(function() {

    $("#menu").change(function() {
        $(this).after('<div id="loader"><img src="images/loading.gif" alt="loading subcategory" /></div>');
        $.get('loadsubcat.php?menu=' + $(this).val(), function(data) {
            $("#sub_cat").html(data);
            $('#loader').slideUp(200, function() {
                $(this).remove();
            });
        }); 
    });

});
</script>
<form name="bid">
    <label for="category">Select Winner</label>
    <select name="willwin" id="menu">

    <?php
if ($db->numrows() > 0){
        while ($row = $db->fetch()) { ?>
        <option value=''></option>
        <option value="<?php echo $row["team1"]; ?>"><?php echo $row["teams1"]; ?></option>
        <option value="<?php echo $row["team2"]; ?>"><?php echo $row["teams2"]; ?></option>
        <?php 
}
}
?>
  • 2 Contributors
  • 10 Replies
  • 564 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by david_105
Member Avatar for david_105 
            <?php 
include('config.php');

$menu = $_GET['menu'];
$query = mysql_query("SELECT a.id, s.team_id, s.teams, u.nick, b.id, b.willwin, b.willlose, b.bidder FROM vs_bids b 
LEFT JOIN " . $DBPrefix . "vs_users u ON (u.id = b.bidder)
LEFT JOIN " . $DBPrefix . "vs_auctions a ON (a.id = b.auction)
LEFT JOIN " . $DBPrefix . "vs_sports s ON (s.team_id = b.willwin)
                    WHERE willlose='$menu' and a.id = b.auction and b.bidder NOT IN ('b.tagged') and b.tagged IN ('b.bidder')");

    while($row = mysql_fetch_array($query)) {
    echo "<option value=''></option>";
    echo "<option value='$row[team_id]'>$row[nick]...$row[teams]</option>";
    }
    ?>

i cant seem to introduce b.auction = :auc_id which will help in displaying the correct result, but have converted it like the menu above but doesnt seem to work, so i converted it back.
please need help with the issue here, thanks.

Member Avatar for diafol

When building complex queries I find it easier to model using a GUI and sample data then building the php code from that. Just an idea.

Member Avatar for diafol

BTW if you're using parameters you need to use a prepared statement with pdo or mysqli. Deprecated mysql functions won't do it.

Edited by diafol
Member Avatar for david_105

have done this 

$stmt = $DB_con->prepare("SELECT a.id, s.team_id, s.teams, u.nick, b.id, b.willwin, b.willlose, b.bidder, b.auction FROM vs_bids b
    LEFT JOIN " . $DBPrefix . "vs_users u ON (u.id = b.bidder)
    LEFT JOIN " . $DBPrefix . "vs_auctions a ON (a.id = b.auction)
    LEFT JOIN " . $DBPrefix . "vs_sports s ON (s.team_id = b.willwin)
    WHERE willlose = :id and auction = :auc_id");

    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->bindParam(':auc_id', $id, PDO::PARAM_INT);
    $stmt->execute();
    ?><option selected="selected">Select State :</option><?php
    while($row=$stmt->fetch(PDO::FETCH_ASSOC))

but didnt work

Edited by david_105
Member Avatar for diafol

but didnt work

Which bit didn't work?
PDO in general?
The query?

If it was the query, changing from pdo to mysql will make no difference. You really need to do some error trapping or do as I suggested with the GUI.

Anyhow that sql bears no resemblence to the one you posted initially. 

...NOT IN ('b.tagged') and b.tagged IN ('b.bidder')

Can't work as you've used single quotes instead of backticks. It should read:

...NOT IN (`b.tagged`) and b.tagged IN (`b.bidder`)

However, IN is expecting a comma separated list of strings or numbers. I wonder if your "IN fields" are that way inclined?

Member Avatar for david_105

...NOT IN (b.tagged) and b.tagged IN (b.bidder)

that doesnt affect what am doing it works fine but in mysql and pdo.

the issue am facing with the code is that it displays all the data in the willwin

for for the menu it displays whats being viewed at the moment when using this
a.id = :auc_id is displays the right result but without it is display all the row in auctions table

if only i could do same with the submenu thats the issue am facing

AUCTIONS.jpg 6.54 KB BIDS.jpg 2.64 KB
Edited by david_105
Member Avatar for diafol

This is the problem with Left Join try inner join maybe

Member Avatar for david_105

didnt work

Member Avatar for diafol

the issue am facing with the code is that it displays all the data in the willwin

and then

didnt work

I'm afraid we don't have a copu of your DB, so we can't see how this is supposed to work. We need a schema of your tables and some sample data and an ide of what ideal output looks like. Still guessing here.

Member Avatar for david_105

ok have included both tables auctions and bids

the submenu output all the datas in the bids table instead of what the id is showing on the top tab

AUCTIONS.jpg 6 KB BIDS.jpg 7.29 KB
Edited by david_105 because: image
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.