Field Name with blanks newbie question 1

[Zych]

Hey Guys and Gals,

I am very new to PHP. I have a table that has field names that has blanks in it like this: Manufacturer Part ID

How do I access this in PHP? I have something like this but it does not work (PartN is passed through the URL):

$query = "SELECT * FROM egov where [Manufacturer Part ID] = '$PartN'";
$result = mysql_query($query);
$PartNumber = mysql_result($result, 0, "[Manufacturer Part ID]");

I had another table that I used this with that had no blanks and this worked fine. I did not have the brackets in that one. Do I need the brackets?

Thanks,

Zych

 

[sleipnir214]

The brackets are an idiosyncracy of Access. MySQL does not recognize it. Try backticks in your query:

$query = "SELECT * FROM egov where `Manufacturer Part ID` = '$PartN'";

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Zych]

I am not sure if that worked or not. I am now getting the following error:

Warning: Unable to jump to row 0 on MySQL result index 2 in /home/httpd/vhosts/lantecsystems.com/httpdocs/catalog/egov.php on line 95

This is on the line that says:

$PartNumber = mysql_result($result, 0, "`Manufacturer Part ID`");

What do I need to change to make this work?

Thanks for the ideas,

Zych

 

[aardvark92]

For testing purposes, try adding these two lines after the $result = line:

$num_rows = mysql_num_rows($result);
echo "$num_rows Rows\n";

Is the query returning any rows?

 

[sleipnir214]

I think the backticks should not be in the column name in your mysql_result() invocation. However, I'm not sure, as I universally use mysql_fetch_assoc() or mysql_fetch_array() to get the column data out of a result row.

Using mysql_fetch_assoc() may help you, too.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Zych]

Taking a closer look at it, it is pulling information where the feild name does not have spaces like 'DESCRIPTION' and 'Image' but not the rest of the feilds like 'Manufacturer Part ID' even though it did use this in the SQL statement (which worked since it pulled one row.) Therefore the backticks worked great in the SQL statement but not in the statements assigning values which have feild names that contain spaces.

So I guess the question now is how do I fix lines like this that have spaces in the feild name?

eg. $PartNumber = mysql_result($result, 0, "`Manufacturer Part ID`");


Thanks,

Zych

 

[sleipnir214]

You have several options.

The first is to modify your table to replace the spaces in columnnames with underscores. In the long run, I strongly recommend this.


If you use mysql_fetch_array() instead of mysql_result(), you can put the values in convenient variables without having to use the column name at all:

$query = "SELECT * FROM egov where [Manufacturer Part ID] = '$PartN'";
$result = mysql_query($query);
[red]list($variable1, $variable2, $variable3) = mysql_fetch_array($result);[/red]

The "gotcha" with this method is that you have to keep track of what columns are returned. If the number of columns returned by the query doesn't match the number of columns in the list(), you'll get an error.


You could also use mysql_fetch_assoc() and reference an array element. Do:

$query = "SELECT * FROM egov where [Manufacturer Part ID] = '$PartN'";
$result = mysql_query($query);
[red]$row = mysql_fetch_assoc($result);[/red]

Then you can reference the value in the array $row as $row['Manufacturer part ID'].


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Zych]

I think the advice to remove the spaces is the best. It now works fine since I have removed them.

Thanks,

Zych