Jump to content

mysql_fetch_array() problem

burkezillar

By burkezillar
in PHP Coding Help

 Share

Recommended Posts

burkezillar Newbie

burkezillar

Posted
Posted
I am making a website, here's the code:

[code]
<?php
$age=$_POST['age'];
$weight=$_POST['weight'];
$bouts=$_POST['bouts'];
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<?php
$link = mysql_connect('mysql2.streamline.net', 'smallheath', '-') or die('Could not connect: ' . mysql_error());
$db_selected = mysql_select_db('smallheath', $link) or die ('Error while connecting to database: ' . mysql_error());
?>


<!--set up the table -->
<table border="1" cellpadding="5">
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
<th>Weight</th>
<th>Bouts</th>
<th>Senior</th>
<th>Gender</th>
</tr>
<?php

// build and execute the query
//Age Query
$query= "SELECT * FROM boxers WHERE age=";
$query .= $age;
$query .= "-1";
$query .= " or age=";
$query .= $age;
$query .= " or age=";
$query .= $age;
$query .= "+1";

//Weight Query
$low=$weight-3;
$high=$weight+3;
$query= "SELECT * FROM boxers WHERE weight >=";
$query .= $low;
$query .= " and weight <=";
$query .= $high;

//Bout Query
$low= "$bouts-3";
$high= "$bouts+3";
$query = "SELECT * FROM boxers WHERE bouts >=";
$query= "$low";
$query= "and bouts <=";
$query= "$high";

$results = array(mysql_query($query));

//loop through results
[b]while ($row = mysql_fetch_array($results)) {[/b]


//get each element and put it in a variable
$row = $fn['first_name'];
$row = $ln['last_name'];
$row = $age['age'];
$row = $weight['weight'];
$row = $bouts['bouts'];
$row = $sen['senior'];
$row = $gen['gender'];

// print out the code for each row
echo <<<END
<tr>
<td>$fn</td>
<td>$ln</td>
<td>$age</td>
<td>$weight</td>
<td>$bouts</td>
<td>$sen</td>
<td>$gen</td>
</tr>
END;
}
?>
<!-- Close Table -->
</table>
</body>
</html>
[/code]

The purpose of this script is to retrieve data from the mysql database, and display the information. It is also supposed to include peoples age's by one year above or below the entered weight, and peoples weights are supposed to include 2kgs either side of the desired weight, and the same with the bouts being searched. I've asked a few people, and nothing seems to work. So I was wondering if you guys here would have a better idea. Thanks for your time!
Link to comment
Share on other sites
GingerRobot Regular Member

GingerRobot

Posted
Posted
Well you probably dont need all of those lines of code for the query, but aside from that, if you are wanted to retreive all the boxers that match your criteria, and show their details, then try:

[code]
<?php
$age=$_POST['age'];
$weight=$_POST['weight'];
$bouts=$_POST['bouts'];
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<?php
$link = mysql_connect('mysql2.streamline.net', 'smallheath', '-') or die('Could not connect: ' . mysql_error());
$db_selected = mysql_select_db('smallheath', $link) or die ('Error while connecting to database: ' . mysql_error());
?>


<!--set up the table -->
<table border="1" cellpadding="5">
   <tr>
      <th>First Name</th>
      <th>Last Name</th>
      <th>Age</th>
      <th>Weight</th>
      <th>Bouts</th>
      <th>Senior</th>
      <th>Gender</th>
   </tr>
<?php

// build and execute the query
//Age Query
$query= "SELECT * FROM boxers WHERE age=";
$query .= $age;
$query .= "-1";
$query .= " or age=";
$query .= $age;
$query .= " or age=";
$query .= $age;
$query .= "+1";

//Weight Query
$low=$weight-3;
$high=$weight+3;
$query= "SELECT * FROM boxers WHERE weight >=";
$query .= $low;
$query .= " and weight <=";
$query .= $high;

//Bout Query
$low= "$bouts-3";
$high= "$bouts+3";
$query = "SELECT * FROM boxers WHERE bouts >=";
$query= "$low";
$query= "and bouts <=";
$query= "$high";

$results = array(mysql_query($query));

//loop through results
while ($row = mysql_fetch_array($results)) {
   // print out the code for each row
   echo <<<END
   <tr>
      <td>$row['first_name']</td>
      <td>$row['last_name'</td>
      <td>$row['age']</td>
      <td>$row['weight']</td>
      <td>$row['bouts']</td>
      <td>$row['senior']</td>
      <td>$row['gender']</td>
   </tr>
END;
}
?>
<!-- Close Table -->
</table>
</body>
</html>
[/code]

Previously, i think you got a little confused with this part:

[code]
<?php
$row = $fn['first_name'];
$row = $ln['last_name'];
$row = $age['age'];
$row = $weight['weight'];
$row = $bouts['bouts'];
$row = $sen['senior'];
$row = $gen['gender'];
?>
[/code]

I think you meant to write:
$fn = $row['first_name'];

etc

Ive not bothered to assign the contents to shorter variable names - unless you're going to be using them later on, it seems like a waste of time.

I think thats what you were wanting.
Link to comment
Share on other sites
michaellunsford Advanced Member

michaellunsford

Posted
Posted
a few things.

first, you keep overwriting your $query string before you execute it. GingerRobot's advice should fix this, unless you're trying to have separate results returned for bout, weight, and age. In that case, you'd need to return results for each query (before you overwrite it) and call $results something like $results['weight'] (so your result doesn't get overwritten each time). You'd also need to refer to it by it's array name in the [code=php:0]mysql_fetch_results($result['weight'])[/code]

second, I'm not quite sure what this is doing
[code]$results = array(mysql_query($query));
[/code]
you shouldn't need to enclose your search results with array(). If there is a purpose to doing it this way, I don't see anything refering to $result as an array anyway -- so you'll probably get nothing (or mysql_fetch_array is probably returning a warning).
Link to comment
Share on other sites
burkezillar Newbie

burkezillar

Posted
  • Author
Posted
I've tried both suggestions, but it is still throwing up that line as a problem. I even changed it to mysql_fetch_results and it still doesn't like it.

I'll give you a bit more information. I'm using PHP 4.1, and the search form I have set up has the following criteria:

Gender (Drop Down Box With Male And Female)
Age
Weight (In Kg's)
Bouts

What I want to happen, is for other boxers close to the searched criteria to be included.

For example,
I want to search for a Male Boxer, Who's 21, Weights 89Kgs, And Has Had 3 Bouts. I would then want the search results to include boxers who:
Are between 20 and 22,
Weigh Between 87Kgs and 91Kgs
And Have Had between 1 bout and 5 bouts.

I asked someone in another forum about this search function, and he gave me all the $query functions. I don't think this causes the problem, as before I added these statements I'd get a problem with that mysql_fetch_array() statement. I Got this code from a PHP and MySQL beginners book, using it as a guideline. I have noticed with some of the code examples, they're written for PHP 5. I found this out when I tried to use the mysqli_connect command, when my server uses PHP 4.1 and this statement isn't supported. This is the exact code that was given in the book:

<!--set up the table -->
<TABLE BORDER="1" CELLPADDING="5">
<TR> 
  <TH>First Name</TH>
  <TH>Last name</TH>
  <TH>Telephone</TH>
  <TH>Email address</TH>
</TR>
<?
//build and execute the query
$select = "SELECT first_name, last_name, tel, email FROM customers";
$result = mysqli_query($link, $select);

//loop through the results
while ($row = mysqli_fetch_array($result)) {
//get each element and put it in a variable
$fn = $row['first_name'];
$ln = $row['last_name'];

//print out the code for each row
echo <<<END
<TR>
  <TD>$fn</TD>
  <TD>$ln</TD>
  <TD>$tel</TD>
  <TD>$email</TD>
</TR>
END;
}
?>
<!-- Close Table -->
</TABLE

Thats the code I copied. So, is this code wrong? Or have I used the wrong code etc?
Link to comment
Share on other sites
GingerRobot Regular Member

GingerRobot

Posted
Posted
Ive made a few changes to your code. Firstly, i think there were a few problems with the query itself. I think the idea was that you had one query, but you had actually started 3 differant ones. So ive modified it into a single query. Second, i was getting some syntax errors with the heredoc system (the <<<END part) and im not overly familiar with it, so to save time, ive changed that. Give this a go.


[code]
<?php
$age=$_POST['age'];
$weight=$_POST['weight'];
$bouts=$_POST['bouts'];
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<?php
$link = mysql_connect('mysql2.streamline.net', 'smallheath', '-') or die('Could not connect: ' . mysql_error());
$db_selected = mysql_select_db('smallheath', $link) or die ('Error while connecting to database: ' . mysql_error());
?>


<!--set up the table -->
<table border="1" cellpadding="5">
  <tr>
      <th>First Name</th>
      <th>Last Name</th>
      <th>Age</th>
      <th>Weight</th>
      <th>Bouts</th>
      <th>Senior</th>
      <th>Gender</th>
  </tr>
<?php

// build and execute the query
//Age Query
$query= "SELECT * FROM boxers WHERE age >=";
$low = $age -1;
$high = $age +1;
$query .= $low;
$query .= " AND `age` <=";
$query .= $high;

//Weight Query
$low=$weight-3;
$high=$weight+3;
$query .= " AND `weight` >=";
$query .= $low;
$query .= " AND `weight` <=";
$query .= $high;

//Bout Query
$low= $bouts-3;
$high= $bouts+3;
$query .= "AND `bouts` >=";
$query .= $low;
$query .= "AND `bouts` <=";
$query .= $high;

$results = mysql_query($query) or die(mysql_error().'<br />QUERY:'.$query);

//loop through results
while ($row = mysql_fetch_array($results)) {
  // print out the code for each row
  echo '
  <tr>
      <td>'.$row['first_name'].'</td>
      <td>'.$row['last_name'].'</td>
      <td>'.$row['age'].'</td>
      <td>'.$row['weight'].'</td>
      <td>'.$row['bouts'].'</td>
      <td>'.$row['senior'].'</td>
      <td>'.$row['gender'].'</td>
  </tr>
';
}
?>
<!-- Close Table -->
</table>
</body>
</html>
[/code]
Link to comment
Share on other sites
burkezillar Newbie

burkezillar

Posted
  • Author
Posted
Ok, cheers for the last code, it's got rid of the mysql_fetch_array problem!!!!!! HORRAYY!!!!

[b]Except![/b]

I'm now getting this:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'bouts` >=-3AND `bouts` <=3' at line 1
QUERY:SELECT * FROM boxers WHERE age >=-1 AND `age` <=1 AND `weight` >=-3 AND `weight` <=3AND `bouts` >=-3AND `bouts` <=3

So yeah, you can guess I'm confused again lol
Link to comment
Share on other sites
GingerRobot Regular Member

GingerRobot

Posted
Posted
Hmm, well i cant see anything really wrong there, perhaps its got a bit confused with the numbers not in quotes and a few missing spaces. Try:
[code]
<?php

// build and execute the query
//Age Query
$query= "SELECT * FROM `boxers` WHERE `age` >=";
$low = $age -1;
$high = $age +1;
$query .= "'$low'";
$query .= " AND `age` <=";
$query .= "'$high'";

//Weight Query
$low=$weight-3;
$high=$weight+3;
$query .= " AND `weight` >=";
$query .= "'$low'";
$query .= " AND `weight` <=";
$query .= "'$high'";

//Bout Query
$low= $bouts-3;
$high= $bouts+3;
$query .= " AND `bouts` >=";
$query .= "'$low'";
$query .= " AND `bouts` <=";
$query .= "'$high'";

$results = mysql_query($query) or die(mysql_error().'<br />QUERY:'.$query);

//loop through results
while ($row = mysql_fetch_array($results)) {
  // print out the code for each row
  echo '
  <tr>
      <td>'.$row['first_name'].'</td>
      <td>'.$row['last_name'].'</td>
      <td>'.$row['age'].'</td>
      <td>'.$row['weight'].'</td>
      <td>'.$row['bouts'].'</td>
      <td>'.$row['senior'].'</td>
      <td>'.$row['gender'].'</td>
  </tr>
';
}
?>
<!-- Close Table -->
</table>
</body>
</html>
[/code]
If that doesn't work, see if you can remove bits of the query until it works to narrow down the problem
Link to comment
Share on other sites
burkezillar Newbie

burkezillar

Posted
  • Author
Posted
I think I have nabbed the problem. The problems are with these query's

$low=$weight-2;
$high=$weight+2;

and

$low= "$bouts-3";
$high= "$bouts+3";

Now, this is just a thought. My server is using MySQL 4.0.20. So I'm thinking these statements aren't supported by this version of MySQL. Would this be a cause? Because I'm damed to know if anything else is...
Link to comment
Share on other sites
GingerRobot Regular Member

GingerRobot

Posted
Posted
Are you sure you're using the script i gave you? Those quotes weren't present in that version. Also, those statements there have nothing to do with those statements, and they dont really have a lot to do with a query either. What makes you think that it is those that are causing the problem?
Link to comment
Share on other sites
burkezillar Newbie

burkezillar

Posted
  • Author
Posted
Well I copied and pasted what you sent me, and it must be what you gave me as it got rid of the array error.

But, I only think those statements could be the problem, because the error message is:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '+3' at line 1
QUERY:+3

So I go to the source code, find anything with +3 in it, and those statements come up.
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

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.