Jump to content

Archived

This topic is now archived and is closed to further replies.

avo

Select Statement Query

Recommended Posts

Hi All

Can anyone please help me out with this puzzle, it is now giving me a headache ive been trying to work this out now for 3 hours

i have a table member_mesages in the table there is an
auto increment column
Username column
Message column

and more but its just these three in question

im using a while statement to echo the usernames into a dropdown list once a name in the dropdown list is selected it is then posted to the username textbox and displayed
the dropdown list is also posted to my SELECT statement but if there is multiple messages from the same user then only the last id of the username is selected and displays in the message text box.

My question is how do i select the user with the same name but different id in my select statement

my code is [code]<?
session_start ();


// includes
require ('admin_header.php');
include ('includes/dbconfig.php');


//connect to db
mysql_connect ($dbhost, $dbuser, $dbpass);
mysql_select_db ($dbname) or die ( mysql_error ());
//get form data for list box
$query = "SELECT username FROM member_messages ORDER BY username ASC";
$result = mysql_query ($query) or die ( mysql_error () );


//get form data for form
$query_all = "SELECT * FROM member_messages WHERE username='".$_POST ['list_box']."'";
$result_all = mysql_query ($query_all) or die ( mysql_error () );
//fetch all other rows

while ( $row = mysql_fetch_assoc($result_all)) {
//assign variable to field names
$frm_id = "{$row['id']}";
$frm_name = "{$row['name']}";
$frm_email = "{$row['email']}";
$frm_message = "{$row['message']}";
$frm_date = "{$row['date']}";
$frm_title = "{$row['title']}";
$frm_message = "{$row['admin_message']}";
}

//when edit user pressed output list box name to username text box field
if ($_POST ['Edit_user']) {
//$_SESSION['$PHP_SELF'];
}

//creates array for all usernames in db
while($nt=mysql_fetch_array($result)) {
echo "<option value='{$nt["username"]}'>{$nt["username"]}</option>";
} [/code]

Share this post


Link to post
Share on other sites
You will want to use the 'id' column to select the user because you will never have the same 'id', but you could have the same 'name'.

So. When populating your select box, you'll want to put the user's 'id' in the value of the <option> and the user's name in the display part.

[code]<?php
echo "<option value='{$nt["id"]}'>{$nt["username"]}</option>";
?>[/code]

Then when you want to pull the user from the db you will use a query like this:

[code]<?php
$query = "SELECT * FROM member_messages WHERE id = $id";
?>[/code]

Where * is whatever columns you want to select. It will then find the 'id' that is equal to $id and give you all the columns requested.

[!--coloro:#990000--][span style=\"color:#990000\"][!--/coloro--]Jeremy[!--colorc--][/span][!--/colorc--]

Share this post


Link to post
Share on other sites
HI

Thankyou

My head is now better allready .

i had to change this line
from this
[code]$query = "SELECT username FROM member_messages ORDER BY username ASC";[/code]
to
[code]$query = "SELECT * FROM member_messages ORDER BY id ";[/code]
and this
[code]echo "<option value='{$nt["username"]}'>{$nt["username"]}</option>";[/code]
to
[code]echo "<option value='{$nt["id"]}'>{$nt["username"]}</option>";[/code]

and this
[code]$query_all = "SELECT * FROM member_messages WHERE username='".$_POST ['list_box']."'"; [/code]
to
[code]$query_all = "SELECT * FROM member_messages WHERE id='".$_POST ['list_box']."'"; [/code]


Thanks again for pointing me in the right direction.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.