$row=mysql_fetch_row

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

meandrew
Forum Commoner
Posts: 49
Joined: Mon Feb 24, 2003 1:03 pm

$row=mysql_fetch_row

Post by meandrew »

Hi All I am trying to display a set of results based on this serch form:

<form action='/companies.php' method='POST' name='CompanySearch'>
<input type='hidden' name='Search' value='YES' />
<input type='hidden' name='CompanySearch' value='Simple' />
<input type='text' class='menuForm' name='ANY' value='Keywords'
maxlength='40' size='13' />

<?
require("connection.php");

mysql_connect("$DBHost","$DBUser","$DBPass");

echo "<select name=\"CityID\" size=\"1\" class='menuForm'>";

$result=mysql("$DBName","SELECT City, CityID FROM city ORDER BY City");
while ($row = mysql_fetch_row($result)) {
echo "<option value=\"$row[1]\"> $row[0] </option>";
}
echo "</select>";

?>


<?

echo "<select name=\"Category\" size=\"1\" class='menuForm'>";

$result=mysql("$DBName","SELECT Category, CategoryID FROM category ORDER BY Category");
while ($row = mysql_fetch_row($result)) {
echo "<option value=\"$row[1]\"> $row[0] </option>";
}
echo "</select>";
?>

</td>
</tr>
<tr class="navTable">
<td class="bgColorMid">
<table cellpadding="0" cellspacing="0" border="0" width="100%"><tr><td valign="bottom"></td>
<td align="right">
<a href="companies.php" class="submitButton">Search</a>
<a href="companies.php" class="submitButton">
<img src="search.gif" border="0" align="absmiddle" vspace="1" hspace="1"></a>
</tr></table>
</td>
</tr>
</form>
</table>

the query then goes like this:

<?
mysql_connect("$DBHost","$DBUser","$DBPass");

$result=mysql_query("SELECT items.ItemSKU, items.ItemName,
items.ItemDescription, items.PostCode, items.Category, items.CityID, items.CTelephone, items.ItemID FROM items WHERE Category='$CA' and CityID='CI' ORDER BY itemName");
if (!$result) echo mysql_error();
else {
}

while ($row=mysql_fetch_row($result)) {
$IS=$row[0];
$IN=$row[1];
$ID=$row[2];
$IC=$row[3];
$Ca=$row[4];
$City=$row[5];
$SC=$row[6];
$II=$row[7];

etc etc


I am having absolutely no joy with this and would really like some help please?

Thank you
Andew
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

Any error messages? What is it not doing correctly?

Cheers,
BDKR (Terrence)
meandrew
Forum Commoner
Posts: 49
Joined: Mon Feb 24, 2003 1:03 pm

$row=mysql_fetch_row

Post by meandrew »

Good Question Terrance I dont know what it's doing exactly :( I dont think its doing anything except moving to the next page companies.php

So I think something is missing but I haven't got the foggiest.

Basicall what i did have working was without using a form and two dropdowns now that I have drop downs I don't get any results.

http://www.punterspower.co.uk/

its the two drop downs

Andrew
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

Well, without seeing error messages, it's allways tought to figure out what's going on in code. That said, perhaps there is a logic issue or display issue. The php engine isn't going to catch those.

Anyways, here are some ramblings.

Here, you have...

Code: Select all

while ($row=mysql_fetch_row($result)) 
{
$IS=$row[0];
$IN=$row[1];
$ID=$row[2];
$IC=$row[3];
$Ca=$row[4];
$City=$row[5];
$SC=$row[6];
$II=$row[7];
}
1) Why not just use mysql_fetch_assoc()?
2) Creating the additional arrays is just doubling the number of vars the system has to work with and slowing things down. If you use *_fetch_assoc(), just echo at the data directly from the array.
3) Try this to hlep with debugging:

Code: Select all

while ($row=mysql_fetch_row($result)) 
    { print_r($row); }
This way you can see for sure if you have data in the rows coming back.
4) use *_num_rows() to see that you got any rows in the first place. The query may have been a success but returned zero results!
5) Look at the source of the generated pages. Sometimes it doesn't show up beause there are no closed tags causing the browser to choke.

I hope this helps.

Cheers,
BDKR
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

Instead of

Code: Select all

<?php
$result=mysql_query("SELECT items.ItemSKU, items.ItemName,
items.ItemDescription, items.PostCode, items.Category, items.CityID, items.CTelephone, items.ItemID FROM items WHERE Category='$CA' and CityID='CI' ORDER BY itemName"); 
?>
try

Code: Select all

<?php
$result=mysql_query("SELECT itemSKU, ItemName,
ItemDescription, PostCode, Category, CityID, CTelephone, ItemID FROM items WHERE Category='$CA' and CityID='CI' ORDER BY itemName"); 
?>
You don't need to declare for each column that it should be fetched from mysql-table "item", as your "SELECT ... FROM items ..." does that anyway.

Don't know if it makes a difference, but it might be worth a try.
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

You don't need to declare for each column that it should be fetched from mysql-table "item", as your "SELECT ... FROM items ..." does that anyway.
This is true as long as only one table is being used. However, if a person regularly pulls from multiple tables, it's a good practice to use the table.column syntax. I'd better explain why.

1) What if your matching records on a column named ID? Something like

Code: Select all

...where user.ID = order.ID...
won't confuse the query engine.

2) What about the poor Joe, yourself included, that has to maintain and possibly
make changes to the query. If there are 83 column names and you don't know which table any of them belongs too, the work needed to figure it out has just shot up 5 or 6 notches.

Cheers,
BDKR (Terrence)
meandrew
Forum Commoner
Posts: 49
Joined: Mon Feb 24, 2003 1:03 pm

$result=mysql_query

Post by meandrew »

thank you guys but none of this is making any difference :(

not even the
while ($row=mysql_fetch_row($result))
{ print_r($row); }




shows anything :(

http://www.punterspower.co.uk

Andrew
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

I see your point, Terrence :)

One last thing that crosses my mind: have you checked if you'd get any results anyway? Try mysql_num_rows().
meandrew
Forum Commoner
Posts: 49
Joined: Mon Feb 24, 2003 1:03 pm

Post by meandrew »

I am getting no results at all, this is the problem :(

Andrew
meandrew
Forum Commoner
Posts: 49
Joined: Mon Feb 24, 2003 1:03 pm

Post by meandrew »

you can see there are no result being returned by checking it

on
http://www.punterspower.co.uk/

Andrew
bionicdonkey
Forum Contributor
Posts: 132
Joined: Fri Jan 31, 2003 2:28 am
Location: Sydney, Australia
Contact:

Post by bionicdonkey »

try changing the array elements from numbers to the field names
meandrew
Forum Commoner
Posts: 49
Joined: Mon Feb 24, 2003 1:03 pm

mysql_query

Post by meandrew »

I now have this:

$result=mysql_query("SELECT items.ItemSKU, items.ItemName,
items.ItemDescription, items.PostCode, items.Category, items.CityID, items.CTelephone, items.ItemID FROM items WHERE Category='$CA' ORDER BY itemName");
if (!$result) echo mysql_error();
else {
}
while ($row=mysql_fetch_row($result)) {
$IS=$row['ItemSKU'];
$IN=$row['ItemName'];
$ID=$row['ItemDescription'];
$IC=$row['PostCode'];
$Ca=$row['Category'];
$City=$row['CityID'];
$SC=$row['CTelephone'];
$II=$row['ItemID'];

and it makes no difference... this is good at least we are isolating what could be the problem for not displaying results from this query.

I have had a thought could the form name be responsible for no results?

this is the code of the form:

<form action='/companies.php' method='POST' name='CompanySearch'>
<input type='hidden' name='Search' value='YES' />
<input type='hidden' name='CompanySearch' value='Simple' />
<input type='text' class='menuForm' name='ANY' value='Keywords'
maxlength='40' size='13' />

<?
require("connection.php");

mysql_connect("$DBHost","$DBUser","$DBPass");

echo "<select name=\"CityID\" size=\"1\" class='menuForm'>";

$result=mysql("$DBName","SELECT City, CityID FROM city ORDER BY City");
while ($row = mysql_fetch_row($result)) {
echo "<option value=\"$row[1]\"> $row[0] </option>";
}
echo "</select>";

?>


<?

echo "<select name=\"Category\" size=\"1\" class='menuForm'>";

$result=mysql("$DBName","SELECT Category, CategoryID FROM category ORDER BY Category");
while ($row = mysql_fetch_row($result)) {
echo "<option value=\"$row[1]\"> $row[0] </option>";
}
echo "</select>";
?>

</td>
</tr>
<tr class="navTable">
<td class="bgColorMid">
<table cellpadding="0" cellspacing="0" border="0" width="100%"><tr><td valign="bottom"></td>
<td align="right">
<a href="companies.php" class="submitButton">Search</a>
<a href="companies.php" class="submitButton">
<img src="search.gif" border="0" align="absmiddle" vspace="1" hspace="1"></a>
</tr></table>
</td>
</tr>
</form>
</table>


Andrew
bionicdonkey
Forum Contributor
Posts: 132
Joined: Fri Jan 31, 2003 2:28 am
Location: Sydney, Australia
Contact:

Post by bionicdonkey »

hmm...found a problem, you have:

Code: Select all

<?php
$result=mysql("$DBName","SELECT City, CityID FROM city ORDER BY City"); 
?>
it should be:

Code: Select all

<?php
$result=mysql_query("$DBName","SELECT City, CityID FROM city ORDER BY City"); 
?>
bionicdonkey
Forum Contributor
Posts: 132
Joined: Fri Jan 31, 2003 2:28 am
Location: Sydney, Australia
Contact:

Post by bionicdonkey »

it might be a good idea to put

Code: Select all

<?php
or die(mysql_error());
?>
after all db related things
meandrew
Forum Commoner
Posts: 49
Joined: Mon Feb 24, 2003 1:03 pm

mysql_query

Post by meandrew »

echo "<select name=\"Category\" size=\"1\" class='menuForm'>";

$result=mysql_query("$DBName","SELECT Category, CategoryID FROM category ORDER BY Category");
while ($row = mysql_fetch_row($result)) {
echo "<option value=\"$row[1]\"> $row[0] </option>";
}
echo "</select>";

after making the change to mysql_query this shows no results in the drop down box?

is this progress? Something says that it is :)

Andrew
Post Reply