MyPHPAdmin - PHP: how do you make userid to username on results?

[jc624]

I have a db that when i get some results i get the user_id like this:

while($report_row = mysql_fetch_array($report_result)){?><tr><td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?>><?php echo getStatus($report_row['status'], $db); ?></td><td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?>><?php echo date("n/d/y g:i a", strtotime($report_row['created'])); ?></td><td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?><?php echo $report_row['user_id']; ?></td>

It works with user_id but I tried username from users in the row but still haven't had luckI have the code like this:

<?php$user_result = mysql_query("SELECT * FROM users ORDER BY username", $db);while($user_row = mysql_fetch_array($user_result)){?><option value="<?php echo $user_row['user_id']; ?>"><?php echo $user_row['username']; ?></option>

The db table is like this:usersand in users theres:Field:user_idusername [sELECT COUNT( * ) AS `Rows` , `username` ]first_namelast_name Anyone know how to add username in the report_row instead of showing them their userid(number)?

[[dx]]

Do you have <select> tag before <option> ?

[jc624]

Do you have <select> tag before <option> ?

I do but this is for the form before i hit submit so i can view the results:<select name="user[]" multiple style="height: 100px;">again I can see this in the form...but what i want to see from the results is the username. I made that td with the userid to see the results of the user that did that report...meaning in the form i can select username: joesmith....i hit submit and in the results:<?php echo $report_row['user_id']; ?>I get joesmith userid # but i want to see in there is "joesmith" the username...of course it doesn't make sense if I search for joesmith but it will make sense when i tried to filter by another query like 'date'...I hope I'm not confusing.Thanks!

[thescientist]

so just replace the user id with usernamefrom this:

<option value="<?php echo $user_row['user_id']; ?>"><?php echo $user_row['username']; ?></option>

to this:

<option value="<?php echo $user_row['username']; ?>"><?php echo $user_row['username']; ?></option>

[jc624]

so just replace the user id with usernamefrom this:

That will just give me this:mysql_num_rows(): supplied argument is not a valid MySQL result resource #.phpSO....

<td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?><?php echo $report_row['user_id']; ?></td>

I just want to know what to input here:<?php echo $report_row['user_id']; ?>how can I change this to username ('username' will define nothing)Am I just missing an edit on the result/report row?:while($report_row = mysql_fetch_array($report_result))

[thescientist]

Ok, I get it now. This is the "long way", haha.sorry, you had the dropdown right before. When the form is submitted, you should be able to select just that user from the table by doing

SELECT * FROM users WHERE user_id = '$_POST['user_id']

which will get you a similar result set that yielded you all the usernames in the first place, except you should only get one user returned. From here you can get their username (again).you can then run the query that you are using to get their extra information, and run the code you had...

while($report_row = mysql_fetch_array($report_result)){?><tr><td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?>><?php echo getStatus($report_row['status'], $db); ?></td><td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?>><?php echo date("n/d/y g:i a", strtotime($report_row['created'])); ?></td><td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?><?php $username ?></td>

and insert the username from the first result set you got.edit: I call it the long way because there are a couple of way you could go around not having to make a second db call just to get the username again. For the option dropdown, you could have it look like this:

<option value="<?php echo $user_row['user_id'] . '|' . $user_row['username']; ?>"><?php echo $user_row['username']; ?></option>

and then use a string function to split it up along the | so you can separate the user_id from the username. Then you just have to make one query for the extra information, and still have the username.

[jc624]

here's the $report_result info

if(!empty($user)){$sql .= "AND ";$sql .= "(";for($i = 0; $i < count($user); $i++){$sql .= "user_id = $user[$i] ";if($i < count($user) - 1){	$sql .= "OR ";}}$sql .= ") ";}$sql .= "ORDER BY $_POST[orderby] ASC";			$report_result = mysql_query($sql, $db);

[birbal]

mysql_num_rows(): supplied argument is not a valid MySQL result resource #.php

where is your mysql_num_rows in your code?here's the $report_result info

CODEif(!empty($user)){$sql .= "AND ";$sql .= "(";for($i = 0; $i < count($user); $i++){$sql .= "user_id = $user[$i] ";if($i < count($user) - 1){ $sql .= "OR ";}}$sql .= ") ";}$sql .= "ORDER BY $_POST[orderby] ASC"; $report_result = mysql_query($sql, $db);

your fragments of codes iare not making sense to me. i think showing us the whole code will worth more rather than assuming.

[jc624]

Sorry the code was too long i placed it below!Thanks birbal and TheScientist!

[jc624]

I guess that was too much here's the whole thing:

<?phpsession_start();if(!isset($_SESSION['logged']))	header("Location: login.php");require 'include/functions.php';	$db = openDatabaseConn();?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html lang="en-US" xml:lang="en-US" xmlns="http://www.w3.org/1999/xhtml"><head>	<title>My DB</title><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><link rel="stylesheet" type="text/css" href="css/style.css"><script type="text/javascript" src="js/script.js"></script></head><body><div id="header">	<?php include 'include/header.php'; ?></div><div id="main_content">	<div id="left_col">		<div id="nav_left">			<?php include 'include/left_nav.php'; ?>		</div>		</div>		<div id="body">		<?php		if(!isset($_POST['report']))		{			?>				<h1>Report Criteria</h1>			<form action="reports.php" method="post">			<input type="hidden" name="report" value="1" />			<div class="fieldset_wide">				<table class="contact_info" cellpadding="0" cellspacing="0">					<tr>						<td colspan="4">											<b>Start:</b> 							<input onclick="cal.select(document.forms[0].date1,'date1','M/dd/yyyy'); return false;" type="text" id="date1" name="date1" style="width: 70px;" />							<a href="#" onclick="cal.select(document.forms[0].date1,'date1','M/dd/yyyy'); return false;"><img border="0" style="margin-right: 10px;" src="images/c.gif" /></a>							<b>End:</b> 							<input onclick="cal.select(document.forms[0].date2,'date2','M/dd/yyyy'); return false;" type="text" id="date2" name="date2" style="width: 70px;" />							<a href="#" onclick="cal.select(document.forms[0].date2,'date2','M/dd/yyyy'); return false;"><img border="0" style="margin-right: 10px;" src="images/c.gif" /></a>											<input type="radio" name="timeframe" value="created" checked /> Created <input type="radio" name="timeframe" value="last_activity" /> Last Activity												</td>					</tr>					<tr>						<td valign="top">							<b>Type:</b><br />							<select name="type[]" multiple>								<option value="0">No Type</option>								<?php								$type_result = mysql_query("SELECT * FROM type", $db);								while($type_row = mysql_fetch_array($type_result))								{									?>									<option value="<?php echo $type_row['type_id']; ?>"><?php echo $type_row['type']; ?></option>									<?php								}								?>							</select>													</td>						<td valign="top">							<b>Status:</b><br />							<select name="status[]" multiple>								<option value="0">No Status</option>															<?php								$status_result = mysql_query("SELECT * FROM status", $db);								while($status_row = mysql_fetch_array($status_result))								{									?>									<option value="<?php echo $status_row['status_id']; ?>"><?php echo $status_row['status']; ?></option>									<?php								}								?>														</select>						</td>						<td valign="top">							<b>Rating:</b><br />							<select name="rating[]" multiple>								<option value="0">No Rating</option>															<?php								$rating_result = mysql_query("SELECT * FROM rating", $db);								while($rating_row = mysql_fetch_array($rating_result))								{									?>									<option value="<?php echo $rating_row['rating_id']; ?>"><?php echo $rating_row['rating']; ?></option>									<?php								}								?>							</select>												</td>						<td valign="top">							<b>Source:</b><br />							<select name="source[]" multiple>								<option value="0">No Source</option>															<?php								$source_result = mysql_query("SELECT * FROM source", $db);								while($source_row = mysql_fetch_array($source_result))								{									?>									<option value="<?php echo $source_row['source_id']; ?>"><?php echo $source_row['source']; ?></option>									<?php								}								?>							</select>												</td>										</tr>					<tr>						<td valign="top">							<b>Location:</b><br />							<select name="location[]" multiple>								<option value="0">No Preference</option>															<?php								$location_result = mysql_query("SELECT * FROM location", $db);								while($location_row = mysql_fetch_array($location_result))								{									?>									<option value="<?php echo $location_row['location_id']; ?>"><?php echo $location_row['location']; ?></option>									<?php								}								?>							</select>																		</td>						<td valign="top">							<b>User:</b><br />							<select name="user[]" multiple style="height: 100px;">								<option value="0">No Preference</option>															<?php								$user_result = mysql_query("SELECT * FROM users ORDER BY username", $db);								while($user_row = mysql_fetch_array($user_result))								{									?>									<option value="<?php echo $user_row['user_id']; ?>"><?php echo $user_row['username']; ?></option>									<?php								}								?>							</select>																		</td>												<td colspan="2" valign="top">							<b>Order By:</b><br />							<select name="orderby">								<option value="last_name">Last Name</option>								<option value="type">Type</option>															<option value="status">Status</option>								<option value="rating">Rating</option>								<option value="source">Source</option>								<option value="created">Create Date</option>															<option value="last_activity">Last Activity</option>							</select>												</td>					</tr>				</table>				<input type="submit" name="submitBTN" value="   Run Report   " />			</div>				</form>				<?php		}		else		{			?>			<h1>Custom Report</h1> <button onclick="location.href = 'reports.php';">New Report</button>			<?php			$date1 = date("Y-m-d H:i:s", strtotime($_POST['date1']));			$date2 = date("Y-m-d H:i:s", strtotime($_POST['date2']));			$type = $_POST['type'];			$status = $_POST['status'];			$rating = $_POST['rating'];			$source = $_POST['source'];			$location = $_POST['location'];			$user = $_POST['user'];						$sql = "SELECT * FROM leads ";			$sql .= "WHERE ";			//if($_SESSION['account_type'] != 1)			//{				//$sql .= "(user_id = $_SESSION[user_id]) AND ";			//}			if(empty($_POST['date1']) || empty($_POST['date2']))			{				$sql .= "($_POST[timeframe] <= NOW()) ";			}			else			{				$sql .= "($_POST[timeframe] >= '$date1' AND $_POST[timeframe] <= '$date2') ";			}						if(!empty($type))			{				$sql .= "AND ";				$sql .= "(";				for($i = 0; $i < count($type); $i++)				{					$sql .= "type = $type[$i] ";					if($i < count($type) - 1)					{						$sql .= "OR ";					}				}				$sql .= ") ";			}			if(!empty($status))			{				$sql .= "AND ";				$sql .= "(";				for($i = 0; $i < count($status); $i++)				{					$sql .= "status = $status[$i] ";					if($i < count($status) - 1)					{						$sql .= "OR ";					}				}				$sql .= ") ";			}							if(!empty($rating))			{				$sql .= "AND ";				$sql .= "(";				for($i = 0; $i < count($rating); $i++)				{					$sql .= "rating = $rating[$i] ";					if($i < count($rating) - 1)					{						$sql .= "OR ";					}				}				$sql .= ") ";			}							if(!empty($source))			{				$sql .= "AND ";				$sql .= "(";				for($i = 0; $i < count($source); $i++)				{					$sql .= "source = $source[$i] ";					if($i < count($source) - 1)					{						$sql .= "OR ";					}				}				$sql .= ") ";			}						if(!empty($location))			{				$sql .= "AND ";				$sql .= "(";				for($i = 0; $i < count($location); $i++)				{					$sql .= "location = $location[$i] ";					if($i < count($location) - 1)					{						$sql .= "OR ";					}				}				$sql .= ") ";			}																				if(!empty($user))			{				$sql .= "AND ";				$sql .= "(";				for($i = 0; $i < count($user); $i++)				{					$sql .= "user_id = $user[$i] ";					if($i < count($user) - 1)					{						$sql .= "OR ";					}				}				$sql .= ") ";			}																				$sql .= "ORDER BY $_POST[orderby] ASC";						$report_result = mysql_query($sql, $db);						$num_rows = mysql_num_rows($report_result);						?>			<div class="fieldset_wide">				<?php				if(!empty($_POST['date1']) && !empty($_POST['date2']))				{					?>					<b>Timeframe</b>: <?php echo $_POST['date1']; ?> to <?php echo $_POST['date2']; ?> (<?php echo ucwords(str_replace("_", " ", $_POST['timeframe'])) ;?>)<br />					<?php				}				?>				<?php				if(!empty($type))				{					?>					<b>Type:</b>					<?php									array_walk($type, "arrayChangeType", $db);					echo implode(", ", $type);					?><br />					<?php				}				?>				<?php				if(!empty($status))				{					?>					<b>Status:</b> 					<?php									array_walk($status, "arrayChangeStatus", $db);					echo implode(", ", $status);					?><br />					<?php				}				?>				<?php				if(!empty($rating))				{					?>									<b>Rating:</b>					<?php									array_walk($rating, "arrayChangeRating", $db);					echo implode(", ", $rating);					?><br />					<?php				}				?>				<?php				if(!empty($source))				{					?>									<b>Source:</b>					<?php									array_walk($source, "arrayChangeSource", $db);					echo implode(", ", $source);					?><br />					<?php				}				?>								<?php				if(!empty($location))				{					?>													<b>Location:</b>					<?php									array_walk($location, "arrayChangeLocation", $db);					echo implode(", ", $location);					?><br />					<?php				}				?>								<?php				if(!empty($user))				{					?>													<b>User:</b>					<?php									array_walk($user, "arrayChangeUser", $db);					echo implode(", ", $user);					?><br />					<?php				}				?>												<br />				<b>Total Rows:</b> <?php echo $num_rows; ?>			</div>						<table class="home_table" cellpadding="0" cellspacing="0">				<tr>					<td class="top">Name</td>					<td class="top">Phone</td>					<td class="top">Email</td>					<td class="top">Type</td>					<td class="top">Status</td>					<td class="top">Rating</td>					<td class="top">Created</td>					<td class="top" style="border-right: 0px;">User</td>									</tr>				<?php				$count = 1;				//echo $sql;				while($report_row = mysql_fetch_array($report_result))				{					?>					<tr>						<td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?>><a class="lead" href="lead.php?lead_id=<?php echo $report_row['lead_id']; ?>"><?php echo $report_row['last_name'] . ', ' . $report_row['first_name']; ?></a></td>						<td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?>><?php echo $report_row['phone']; ?></td>						<td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?>><a class="lead" href="email.php?lead_id=<?php echo $report_row['lead_id']; ?>"><?php echo $report_row['email']; ?></a></td>						<td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?>><?php echo getTypeName($report_row['type'], $db); ?></td>						<td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?>><?php echo getStatus($report_row['status'], $db); ?></td>						<td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?>><?php echo getRating($report_row['rating'], $db); ?></td>						<td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?>><?php echo date("n/d/y g:i a", strtotime($report_row['created'])); ?></td>						<td <?php if(($count % 2) == 0) echo 'class="alt_row"'; ?> style="border-right: 0px;"><?php echo $report_row['user_id']; ?></td>					</tr>					<?php					$count++;				}				$url_sql = urlencode($sql);				?>			</table>			<button onclick="location.href='export_report.php?sql=<?php echo $url_sql; ?>';">Export to Excel</button>			<?php		}		?>			</div></div><div style="clear: both;"></div><div id="footer">	<?php include 'include/footer.php'; ?>	</div></body></html>

[jc624]

anyone have any ideas ??thanks