Page 1 of 3

$row=mysql_fetch_row

Posted: Mon Feb 24, 2003 1:03 pm
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

Posted: Mon Feb 24, 2003 1:17 pm
by BDKR
Any error messages? What is it not doing correctly?

Cheers,
BDKR (Terrence)

$row=mysql_fetch_row

Posted: Mon Feb 24, 2003 1:24 pm
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

Posted: Mon Feb 24, 2003 1:51 pm
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

Posted: Mon Feb 24, 2003 1:52 pm
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.

Posted: Mon Feb 24, 2003 2:01 pm
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)

$result=mysql_query

Posted: Mon Feb 24, 2003 2:09 pm
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

Posted: Mon Feb 24, 2003 2:16 pm
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().

Posted: Mon Feb 24, 2003 2:21 pm
by meandrew
I am getting no results at all, this is the problem :(

Andrew

Posted: Mon Feb 24, 2003 2:23 pm
by meandrew
you can see there are no result being returned by checking it

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

Andrew

Posted: Mon Feb 24, 2003 3:07 pm
by bionicdonkey
try changing the array elements from numbers to the field names

mysql_query

Posted: Mon Feb 24, 2003 3:37 pm
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

Posted: Mon Feb 24, 2003 3:41 pm
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"); 
?>

Posted: Mon Feb 24, 2003 3:43 pm
by bionicdonkey
it might be a good idea to put

Code: Select all

<?php
or die(mysql_error());
?>
after all db related things

mysql_query

Posted: Mon Feb 24, 2003 3:47 pm
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