please help with pulling data from database...

[justinmyoung]

I'm trying pull up data from database, and display it on a table. I used this code before but for some reason it won't work here. Any suggestions? Thanks!

It says the error is this line: <?php while ($list = mysql_fetch_assoc($result)) {?> The error message is: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /Users/laurenyoung/Sites/runners.php on line 60

<?php

// Include connection to your database
	$con = mysql_connect("","","");
	mysql_select_db("RUNNERS", $con);
	
	$name = $_POST['name']; 
	$query = "SELECT * FROM runners WHERE id = '$name'";
	$result = mysql_query($query);?>

	<table class="sortable">
	<!-- Table Header -->
	<thead>
	<tr>
	<th>First Name</th>
	<th>Last Name</th>
	<th>1 Mile</th>
	<th>2 Mile</th>
	<th>5k</th>
	<th>8k</th>
	<th>10k</th>
	<th>15k</th>
	<th>20k</th>
	<th>13.1</th>
	<th>25k</th>
	<th>26.2</th>
	</tr>
	</thead>

<!-- Table body-->
	<tbody>
	<?php while ($list = mysql_fetch_assoc($result)) {?>
	<tr>
	<th><?php echo $list['first_name'] ?></th>
	<th><?php echo $list['last_name'] ?></th>
	<th><?php echo $list['1_mile'] ?></th>
	<th><?php echo $list['2_mile'] ?></th>
	<th><?php echo $list['5k'] ?></th>
	<th><?php echo $list['8k'] ?></th>
	<th><?php echo $list['10k'] ?></th>
	<th><?php echo $list['15k'] ?></th>
	<th><?php echo $list['20k'] ?></th>
	<th><?php echo $list['half'] ?></th>
	<th><?php echo $list['25k'] ?></th>
	<th><?php echo $list['full'] ?></th>
	</tr>
	<?php } ?>
	</tbody>


</table>

[mustafaneguib]

try to use

<?php while ($list = mysql_fetch_array($result)) {?>

instead of,

<?php while ($list = mysql_fetch_assoc($result)) {?>

[justinmyoung]

Still got the same error, but thanks for trying. Perhaps it's something wrong with the code I use on the initial page, which looks like this:

<form action="runners.php" method="post">

		   
		
	
	<label for="first_name" style="font-size: 14px; margin-top: 10px; font-weight: bold; font-variant: small-caps;">First Name:</label>
	<input type="text" name="name" value="" maxlength="30" style="width:49.6%; font-size: 30px;"<br /><?php if    (isset($_POST['first_name'])) echo $_POST['first_name']; ?><br /></p>
	<label for="last_name" style="font-size: 14px; margin-top: 10px; font-weight: bold; font-variant: small-caps;">Last Name:</label>
	<input type="text" name="storeid" value="" maxlength="30" style="width:49.6%; font-size: 30px;"<br /><?php if    (isset($_POST['last_name'])) echo $_POST['last_name']; ?><br /></p>
	
	<form method="post" action="runners.php">
	
	<input type="submit" style="width:90px; height: 35px; margin-left: 260px; -moz-border-radius: 10px;" name="submit" value="get info">
</form>

Thanks again!

[justinmyoung]

Well I got it to come up with no errors, but it's not pulling the data:

<form action="runners.php" method="post">

		   
		
	
	<label for="first_name" style="font-size: 14px; margin-top: 10px; font-weight: bold; font-variant: small-caps;">First Name:</label>
	<input type="text" name="first_name" value="" maxlength="30" style="width:49.6%; font-size: 30px;"<br /><?php if    (isset($_POST['first_name'])) echo $_POST['first_name']; ?><br /></p>
	<label for="last_name" style="font-size: 14px; margin-top: 10px; font-weight: bold; font-variant: small-caps;">Last Name:</label>
	<input type="text" name="storeid" value="" maxlength="30" style="width:49.6%; font-size: 30px;"<br /><?php if    (isset($_POST['last_name'])) echo $_POST['last_name']; ?><br /></p>
	
	<form method="post" action="runners.php">

<?php
	// Include connection to your database
	$con = mysql_connect("localhost","root","");
	mysql_select_db("RUNNERS", $con);
	
	$name = $_POST['first_name']; 
	$query = "SELECT * FROM runners WHERE id = '$name'";
	$result = mysql_query($query);?>

	<table class="sortable">
	<!-- Table Header -->
	<thead>
	<tr>
	<th>ID #</th>
	<th>First Name</th>
	<th>Last Name</th>
	<th>1 Mile</th>
	<th>2 Mile</th>
	<th>5k</th>
	<th>8k</th>
	<th>10k</th>
	<th>20k</th>
	<th>13.1</th>
	<th>26.2</th>
	</tr>
	</thead>




	<!-- Tabel body-->
	<tbody>
	<?php while ($list = mysql_fetch_assoc($result)) {?>
	<tr>
	<th><?php echo $list['first_name'] ?></th>
	<th><?php echo $list['last_name'] ?></th>
	<th><?php echo $list['one'] ?></th>
	<th><?php echo $list['two'] ?></th>
	<th><?php echo $list['five'] ?></th>
	<th><?php echo $list['ten'] ?></th>
	<th><?php echo $list['fifteen'] ?></th>
	<th><?php echo $list['twenty'] ?></th>
	<th><?php echo $list['half'] ?></th>
	<th><?php echo $list['full'] ?></th>
	</tr>
	<?php } ?>
	</tbody>


</table>

Thanks for any help!

[praveen_dusari]

hi,
echo your sql query. you didn`t put die() it will display error if database is selected or not

[Shanti C]

change this :

$query = "SELECT * FROM runners WHERE id = '$name'";

To:

$query = "SELECT * FROM runners WHERE id =".$name;

[justinmyoung]

1. Looks like I'm connecting fine. I added the die function.

2. I changed $query = "SELECT * FROM runners WHERE id = '$name'"; to $query = "SELECT * FROM runners WHERE id =".$name; , but I'm receiving this error: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /Users/laurenyoung/Sites/runners.php on line 67. Line 67 = <?php while ($list = mysql_fetch_assoc($result)) ?> Here's how I have the code as it stands now:

<?php
	// Include connection to your database
	$con = mysql_connect("localhost","root","");
	mysql_select_db("RUNNERS", $con);
	
	if (!$con)
	  {
	  die('Could not connect: ' . mysql_error());
	  }
	
	$name = $_POST['first']; 
	$query = "SELECT * FROM runners WHERE id =".$name;
	$result = mysql_query($query);?>

<table class="sortable">
	<!-- Table Header -->
	<thead>
	<tr>
	<th>ID #</th>
	<th>First Name</th>
	<th>Last Name</th>
	<th>1 Mile</th>
	<th>2 Mile</th>
	<th>5k</th>
	<th>8k</th>
	<th>10k</th>
	<th>20k</th>
	<th>13.1</th>
	<th>26.2</th>
	</tr>
	</thead>

<!-- Table body-->
	<tbody>
	<?php while ($list = mysql_fetch_assoc($result)) ?>
	<tr>
	<th><?php echo $list['first_name'] ?></th>
	<th><?php echo $list['last_name'] ?></th>
	<th><?php echo $list['one'] ?></th>
	<th><?php echo $list['two'] ?></th>
	<th><?php echo $list['five'] ?></th>
	<th><?php echo $list['ten'] ?></th>
	<th><?php echo $list['fifteen'] ?></th>
	<th><?php echo $list['twenty'] ?></th>
	<th><?php echo $list['half'] ?></th>
	<th><?php echo $list['full'] ?></th>
	</tr>
	</tbody>


</table>

That's the only error I'm receiving, however. I would like to have the user enter his/her first and last name to pull the data. I thought that was how I had it written, but it is still, obviously, given me trouble.

Thanks!

[Shanti C]

so you dont have any colums with that name $_POST in you table...

first execute your query in mysql with the same name,and put in your php page...

[R0bb0b]

put in more die functions.

<?php
	// Include connection to your database
	$con = mysql_connect("localhost","root","") or die(mysql_error());;
	mysql_select_db("RUNNERS", $con) or die(mysql_error());
	
	$name = $_POST['first']; 
	$query = "SELECT * FROM runners WHERE id =".$name;
	$result = mysql_query($query) or die(mysql_error());?>

<table class="sortable">
	<!-- Table Header -->
	<thead>
	<tr>
	<th>ID #</th>
	<th>First Name</th>
	<th>Last Name</th>
	<th>1 Mile</th>
	<th>2 Mile</th>
	<th>5k</th>
	<th>8k</th>
	<th>10k</th>
	<th>20k</th>
	<th>13.1</th>
	<th>26.2</th>
	</tr>
	</thead>

<!-- Table body-->
	<tbody>
	<?php while ($list = mysql_fetch_assoc($result)) ?>
	<tr>
	<th><?php echo $list['first_name'] ?></th>
	<th><?php echo $list['last_name'] ?></th>
	<th><?php echo $list['one'] ?></th>
	<th><?php echo $list['two'] ?></th>
	<th><?php echo $list['five'] ?></th>
	<th><?php echo $list['ten'] ?></th>
	<th><?php echo $list['fifteen'] ?></th>
	<th><?php echo $list['twenty'] ?></th>
	<th><?php echo $list['half'] ?></th>
	<th><?php echo $list['full'] ?></th>
	</tr>
	</tbody>


</table>

I agree with Shanti, there is something wrong with your query and this should tell you what it is.

[mustafaneguib]

i think ive found the problem. ur using while loop when your only looking for one (as far as i can see) record. so remove the while statement, and use the following,

$list=mysql_fetch_array($result);

instead of it because your problem lies in this line.

<!-- Table body-->
	<tbody>
	<?php  $list=mysql_fetch_array($result); ?> //change this to this
	<tr>
	<th><?php echo $list['first_name'] ?></th>
	<th><?php echo $list['last_name'] ?></th>
	<th><?php echo $list['one'] ?></th>
	<th><?php echo $list['two'] ?></th>
	<th><?php echo $list['five'] ?></th>
	<th><?php echo $list['ten'] ?></th>
	<th><?php echo $list['fifteen'] ?></th>
	<th><?php echo $list['twenty'] ?></th>
	<th><?php echo $list['half'] ?></th>
	<th><?php echo $list['full'] ?></th>
	</tr>
	</tbody>


</table>

[mustafaneguib]

you can use mysql_fetch_assoc in the following.
this is wat i know but i may be wrong.

while( $list=mysql_fetch_assoc($result))
{
 foreach($list as $val)
{
echo $val;
}
echo "<br />";
}

[justinmyoung]

I added the extra die functions, and I tried to change $list=mysql_fetch_array($result); to this

while( $list=mysql_fetch_assoc($result))
{
 foreach($list as $val)
{
echo $val;
}
echo "<br />";
}

but it's not working.

Instead, I'm getting a strange error that says: Unknown column 'Justin' in 'where clause'

Justin is the test name I have in the database. I'm stumped.

So what I have now looks like this:

<?php
// Include connection to your database
$con = mysql_connect("localhost","root","rilke123");
mysql_select_db("RUNNERS", $con) or die(mysql_error());

$name = $_POST['first']; 
$query = "SELECT * FROM runners WHERE id =".$name;
$result = mysql_query($query) or die(mysql_error());?>


<table class="sortable">
<!-- Table Header -->
<thead>
<tr>
<th>ID #</th>
<th>First Name</th>
<th>Last Name</th>
<th>1 Mile</th>
<th>2 Mile</th>
<th>5k</th>
<th>8k</th>
<th>10k</th>
<th>20k</th>
<th>13.1</th>
<th>26.2</th>
</tr>
</thead>




<!-- Table body-->
    <tbody>
    <?php while ($list = mysql_fetch_assoc($result)) ?>
    <tr>
    <th><?php echo $list['first_name'] ?></th>
    <th><?php echo $list['last_name'] ?></th>
    <th><?php echo $list['one'] ?></th>
    <th><?php echo $list['two'] ?></th>
    <th><?php echo $list['five'] ?></th>
    <th><?php echo $list['ten'] ?></th>
    <th><?php echo $list['fifteen'] ?></th>
    <th><?php echo $list['twenty'] ?></th>
    <th><?php echo $list['half'] ?></th>
    <th><?php echo $list['full'] ?></th>
    </tr>
    </tbody>

Thanks again!

[R0bb0b]

echo your query and post it.

[justinmyoung]

not sure how to echo my query. as simple as it may be...

thanks again!

[R0bb0b]

right under this line $query = "SELECT * FROM runners WHERE id = '$name'"; put echo $query; run it, copy the results, and post it.

[justinmyoung]

This is what I got:

SELECT * FROM runners WHERE id =JustinUnknown column 'Justin' in 'where clause'

I only have 1 column of data in the database. Justin is the first name.

[justinmyoung]

it seems like it has something to do with the 'while' loop not wrapping around the code...?

[mustafaneguib]

can u copy and past ur table structure here. i think there is a problem in there.

the id which you are using in
where id=$name

is id an integer field or a string field? because id is usually used as an integer and is a unique value.

[justinmyoung]

Here's the table structure:

CREATE TABLE IF NOT EXISTS `runners` (
  `id` mediumint(50) unsigned NOT NULL auto_increment,
  `first_name` varchar(50) NOT NULL,
  `last_name` smallint(50) NOT NULL,
  `one` smallint(50) NOT NULL,
  `two` smallint(50) NOT NULL,
  `five` smallint(50) NOT NULL,
  `ten` smallint(50) NOT NULL,
  `fifteen` smallint(50) NOT NULL,
  `twenty` smallint(50) NOT NULL,
  `half` smallint(50) NOT NULL,
  `full` smallint(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `runners`
--

INSERT INTO `runners` (`id`, `first_name`, `last_name`, `one`, `two`, `five`, `ten`, `fifteen`, `twenty`, `half`, `full`) VALUES
(1, 'Justin', 0, 400, 800, 1300, 2700, 4500, 5800, 6000, 20000);

[mustafaneguib]

ive found ur mistake. ur using
the following,

$name = $_POST['first']; 
$query = "SELECT * FROM runners WHERE id =".$name;

do this,

$name = $_POST['first']; 
$query = "SELECT * FROM runners WHERE first_name =".$name;

u are using the wrong field. u are comparing an int field with string and ur are comparing the id field with the name.

[R0bb0b]

This is what I got:

SELECT * FROM runners WHERE id =JustinUnknown column 'Justin' in 'where clause'

I only have 1 column of data in the database. Justin is the first name.

mysql is considering justin a column because you are not single quoting it. if id is suppose to be numeric then can process it without single quotes but I do it anyway for security purposes.

[mustafaneguib]

mysql is considering justin a column because you are not single quoting it. if id is suppose to be numeric then can process it without single quotes but I do it anyway for security purposes.

like i said in my previous post. he is using the wrong field to query. he is suppoed to use first_name field instead of the id field.

[R0bb0b]

ive found ur mistake. ur using
the following,

$name = $_POST['first']; 
$query = "SELECT * FROM runners WHERE id =".$name;

do this,

$name = $_POST['first']; 
$query = "SELECT * FROM runners WHERE first_name =".$name;

u are using the wrong field. u are comparing an int field with string and ur are comparing the id field with the name.

mysql is still going to parse the name as a column because of the lack of single quotes

[mustafaneguib]

mysql is still going to parse the name as a column because of the lack of single quotes

ya ur right there should be single quotes. that is one mistake that should be also rectified.

btw its fun trying to solve a problem. :)

[justinmyoung]

I'm not exactly sure where to put the single quotes. I changed the where clause, but it seems to be giving the same error:

<?php
// Include connection to your database
$con = mysql_connect("localhost","root","");
mysql_select_db("RUNNERS", $con) or die(mysql_error());

$name = $_POST['first_name']; 
$query = "SELECT * FROM runners WHERE first_name =".$name; 
$result = mysql_query($query) or die(mysql_error());?>


<table class="sortable">
<!-- Table Header -->
<thead>
<tr>
<th>ID #</th>
<th>First Name</th>
<th>Last Name</th>
<th>1 Mile</th>
<th>2 Mile</th>
<th>5k</th>
<th>8k</th>
<th>10k</th>
<th>20k</th>
<th>13.1</th>
<th>26.2</th>
</tr>
</thead>

<!-- Table body-->
<tbody>
<?php while ($list = mysql_fetch_assoc($result)) ?>
<tr>
<th><?php echo $list['id'] ?></th>
<th><?php echo $list['first_name'] ?></th>
<th><?php echo $list['last_name'] ?></th>
<th><?php echo $list['one'] ?></th>
<th><?php echo $list['two'] ?></th>
<th><?php echo $list['five'] ?></th>
<th><?php echo $list['ten'] ?></th>
<th><?php echo $list['fifteen'] ?></th>
<th><?php echo $list['twenty'] ?></th>
<th><?php echo $list['half'] ?></th>
<th><?php echo $list['full'] ?></th>
</tr>

</tbody>

If I enter the name "Justin", I get this error: Unknown column 'Justin' in 'where clause'
But if I enter any number, I do not get an error, but a list of the table headers.

Thanks for both of your help...

[nav33n]

If you are dealing with a string in your query, that is,

$query = "select * from table where name='test'";
$query = "select * from table where date='2008-01-20'";
//...etc

then you are supposed to use single quotes around them.
If you are using an integer, then you don't need those quotes. Eg.

$query = "select * from table where id=3";

So, your query has to be modified a lil bit.

$query = "SELECT * FROM runners WHERE first_name ='".$name."'";

Notice the single quotes around $name. You can also do it this way.

$query = "SELECT * FROM runners WHERE first_name ='$name'";

I hope its clear now.

P.S. Also, since you are using a loop, its always good if you put them in a block. ie., use { } .

while($row = mysql_fetch_array($result)) {
 //something here
}

[justinmyoung]

That makes sense. The correct quotes solved the error, but for some reason the code is still not pulling the data from the database. I just get the table headers, but no data!

[R0bb0b]

echo you query and run it in your mysql manager and see if that pulls data, that way you can eliminate that as an issue.

[justinmyoung]

How do you put a loop within a block for this portion of the code?

<?php while ($list = mysql_fetch_assoc($result)) ?>
<tr>
<th><?php echo $list['id'] ?></th>
<th><?php echo $list['first_name'] ?></th>
<th><?php echo $list['last_name'] ?></th>
<th><?php echo $list['one'] ?></th>
<th><?php echo $list['two'] ?></th>
<th><?php echo $list['five'] ?></th>
<th><?php echo $list['ten'] ?></th>
<th><?php echo $list['fifteen'] ?></th>
<th><?php echo $list['twenty'] ?></th>
<th><?php echo $list['half'] ?></th>
<th><?php echo $list['full'] ?></th>
</tr>

</tbody>

It seems this is the last obstacle...

[mustafaneguib]

<?php while ($list = mysql_fetch_assoc($result)) {?>
<tr>
<th><?php echo $list['id'] ?></th>
<th><?php echo $list['first_name'] ?></th>
<th><?php echo $list['last_name'] ?></th>
<th><?php echo $list['one'] ?></th>
<th><?php echo $list['two'] ?></th>
<th><?php echo $list['five'] ?></th>
<th><?php echo $list['ten'] ?></th>
<th><?php echo $list['fifteen'] ?></th>
<th><?php echo $list['twenty'] ?></th>
<th><?php echo $list['half'] ?></th>
<th><?php echo $list['full'] ?></th> 
</tr>
<?php } ?>
</tbody>