Getting Specific Field from a table??

[unknowngiver]

Hey
I am trying to get a SPECIFIC field from a table..
for example if the table has these fields:

username, pass, health, country, offense

and i only want the page to display "health" of a user..how would i do that??

 

[iNod]

$select = mysql_query("SELECT health FROM somewhere");

- Steve

 

[Peter]

yes as iNod demonstrates you can just select a single field. But just because you select more than 1 field does not mean it has to be shown to the user.

 

[unknowngiver]

hm how would i do that Filth?
and after i have it selected do i just do

echo $select

 

[Peter]

<?php
$query = 'SELECT * FROM `table_name` LIMIT 1';

$execute_query = mysql_query($query);

$get_row = mysql_fetch_assoc($execute_query);

echo $get_row['health']
?>

obviously before this code you would need to connect to the database. Also remember to change table_name to the real table name. If you would like to use another field in that table then you would simply use $get_row['field_name'] replacing field_name with the actual field name.

 

[unknowngiver]

yes but it will get the health of the first user or something? where do i SPECIFIY that get the field of that user...
for example
get the health of a user called "Justin"

 

[Peter]

simply change the select query to:-

SELECT * FROM `table_name` WHERE `username` = 'justin' LIMIT 1

If you are wanting to select information for all users then it is more of a case of running through a while loop.

 

[PoorDoggie]

getting one user's health:

$sql = "SELECT * FROM table_name WHERE username= 'justin'";
$result = mysql_query($sql);
$row = mysql_fetch_array($result); // don't know the difference between array and assoc they both do the same I would imagine...
echo "Justin's health is: ".$row['health'];

getting the result for all users:

$sql = "SELECT * FROM table_name";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
  echo $row['name']." health is: ".$row['health']."<br />";
}

Hope this helps!

Tom

 

[unknowngiver]

Great ..but whats $row in ur while loop? u dont need to define it?

 

[Peter]

don't know the difference between array and assoc they both do the same I would imagine...

with mysql_fetch_array() you can select wether to be able to use field reult number or field name in the resultant array (or even both) with mysql_fetch_assoc it only uses the field name (or name specified in the query)

Great ..but whats $row in ur while loop? u dont need to define it?

he already did in the start of the while loop. It contains the data for 1 row at a time, after it process the code for the first entry it does the same for the second then the thrid etc until it has finished. So if you get 20 rows it will execute the code in the while loop 20 times with each rows own data,

 

[unknowngiver]

o okay
so if i am right :


$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){


$result will be the amount of rows in that table...

and the while statement means
that while the $result has a value...run this loop
right??

 

[Peter]

no if you want the number of rows then you will need to use the mysql_num_rows() function.

And yes you are basically correct regarding $result

 

[unknowngiver]

o okay
okay well one more question:

For health...how can i insert MATH into the field
like i want to
subtract "x" from w.e the field health has so far...or add something to it??

and also is this an array

$get_row = mysql_fetch_assoc($execute_query);

i asked because later on it says $get_row['health']

 

[nasaboy007]

u wud prolly have to get that value check that its numeric using is_numeric(), perform the math to it (add, subtract, etc), and then write it into the field again, overwriting the old one.

 

[unknowngiver]

hey
first of al
i got an error by using that code:
this is what i get

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in D:\htdocs\Zubair\details.php on line 13
????

and this is my code

<?
$user = $_GET['user'];
$detailpass = $_GET['detailpass'];
$action = $_GET['action'];
$dbusername="zubair";
$dbpassword="";
$database= "z_game";
if ($detailpass == "aa") {
	mysql_connect(localhost, $dbusername, $dbpassword);
	@mysql_select_db($database) or die("Unable to select database");
	$sql = mysql_query("Select * From user where username = ".$user." LIMIT 1")  ;
	
	$get_row = mysql_fetch_assoc($sql);
	
		if ($action = "list"){
	echo $get_row['health'];
	echo "?";
	echo $get_row['money'];
	echo "?";
	echo $get_row['country'];
	echo "?";
	echo $get_row['level'];
	echo "?";
	} }
else {
echo "error\n";
}
?>

Dont ask y i m using $_gets..i am making a game in TURING [unpopular programming language] and i need those...

 

[Peter]

it is possible that there were no records to show so when using the mysql_fetch_assoc it was trying to pull from nothing.

 

[unknowngiver]

o okay
well i got that fixed
but got another question
what if i want to echo more then 1 output...like select 3 [random or w.e] users that have the country "canada" ?

 

[Dan]

SELECT rand() FROM table WHERE country = "canada" LIMIT 3

 

[unknowngiver]

k so i tried this

<?
$country = $_GET['country'];
$searchpass = $_GET['searchpass'];
$dbusername="zubair";
$dbpassword="";
$database= "z_game";
if ($searchpass == "aa") {
	mysql_connect(localhost, $dbusername, $dbpassword);
	@mysql_select_db($database) or die("Unable to select database");
	 $sql = 'SELECT * FROM `user` WHERE country = "pakistan" LIMIT 1';
	$execute = mysql_query($sql);

echo $execute;
 }
else {
echo "error\n";
}
?>

and it gave me this:

Resource id #3

 

[Dan]

<?
$country = $_GET['country'];
$searchpass = $_GET['searchpass'];
$dbusername="zubair";
$dbpassword="";
$database= "z_game";
if ($searchpass == "aa") {
	mysql_connect(localhost, $dbusername, $dbpassword);
	@mysql_select_db($database) or die("Unable to select database");
	$sql = 'SELECT * FROM `user` WHERE country = "pakistan" LIMIT 1';
	$execute = mysql_query($sql);
	while ($row = mysql_fetch_assoc($result)) {
		echo 'Username: ' . $row['username.'] . '<br />Health: ' . $row['health'] . '<br />Money: ' . $row['money'];
	}
}
else {
echo "error\n";
}
?>