Jump to content


Photo

Select Statement Query


  • Please log in to reply
2 replies to this topic

#1 avo

avo
  • Members
  • PipPipPip
  • Advanced Member
  • 148 posts
  • Locationstaffordshire uk

Posted 08 May 2006 - 07:50 PM

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
<?
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>"; 
}

Im loving it ........

#2 jeremywesselman

jeremywesselman
  • Members
  • PipPipPip
  • Advanced Member
  • 154 posts
  • LocationIndependence, KY

Posted 08 May 2006 - 09:23 PM

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.

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

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

<?php
$query = "SELECT * FROM member_messages WHERE id = $id";
?>

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--]

#3 avo

avo
  • Members
  • PipPipPip
  • Advanced Member
  • 148 posts
  • Locationstaffordshire uk

Posted 08 May 2006 - 10:01 PM

HI

Thankyou

My head is now better allready .

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

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


Thanks again for pointing me in the right direction.
Im loving it ........




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users