Getting A Complete List From A PHP Query

[New2Biz]

As previously stated, I am brand new to PHP and have been relying on books to learn how to use it. That being the case, I am having trouble customizing it. Any help is really appreciated because I dont know what I am doing wrong.

Here is the scenario. I have a database that is populated from one page, and am trying to create a page where a user can filter based on their search criteria...and the results would be pasted on a page.

In particular, what I am trying to do is have each of the following database fields listed, for each company meeting the search criteria, in the result (Company, website, fundsneeded, startup, contact, email, addressone, addresstwo, city, stateprovince, postalcode, country, pitch, addlinfo, addlinfotwo ).

In this instance, bizpitch is the database, and bizpitches is the table

I think I have the code for the search criteria correct, but have pasted it below. That page can be viewed at

http://www.bizpitches.com/bizpitches.php

. I will also list the code that i have written for the display page, which is the page I think I am really botching the code up. Any help in identifying where my code is wrong would be so helpful.
Mike

Code for Search Criteria Page (the page I think I have the correct code for)

<?php

$dbcnx = @mysql_connect(XXXXXXX, 'XXXXXXX', 'XXXXXXXX');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' .
'database server at this time.</p>');
}

if (!@mysql_select_db('bizpitch')) {
exit('<p>Unable to locate the bizlisting ' .
'database at this time.</p>');
}

$companies = @mysql_query('SELECT id, company FROM bizpitches');
if (!$companies) {
exit('<p>Unable to obtain company list from the database.</p>');
}
$industries = @mysql_query('SELECT id, industry FROM bizpitches');
if (!$industries) {
exit('<p>Unable to obtain industry list from the database.</p>');
}

$startups = @mysql_query('SELECT id, startup FROM bizpitches');
if (!$startups) {
exit('<p>Unable to obtain status from the database.</p>');
}

$locations = @mysql_query('SELECT id, stateprovince FROM bizpitches');
if (!$locations) {
exit('<p>Unable to obtain location list from the database.</p>');
}

$funds = @mysql_query('SELECT id, fundsneeded FROM bizpitches');
if (!$funds) {
exit('<p>Unable to obtain funding information from the database.</p>');
}
?>
<form action="bizlist.php" method="post">
<h2>View business pitches that meet the following criteria:</h2>
<label>By Company Name:
<select name="cid" size="1">
<option selected value="">Any Company</option>
<?php
while ($bizpitches = mysql_fetch_array($companies)) {
$cid = $bizpitches['id'];
$cname = htmlspecialchars($bizpitches['company']);
echo "<option value='$cid'>$cname</option>\n";
}
?>
</select></label><br />
<label><br>By Industry:
<select name="iid" size="1">
<option selected value="">Any Industry</option>
<?php
while ($bizpitches = mysql_fetch_array($industries)) {
$iid = $bizpitches['id'];
$iname = htmlspecialchars($bizpitches['industry']);
echo "<option value='$iid'>$iname</option>\n";
}
?>
</select></label><br />
<label><br>Startup (Y/N):
<select name="sid" size="1">
<option selected value="">Any Stage</option>
<?php
while ($bizpitches = mysql_fetch_array($startups)) {
$sid = $bizpitches['id'];
$sname = htmlspecialchars($bizpitches['startup']);
echo "<option value='$sid'>$sname</option>\n";
}
?>
</select></label><br />
<label><br>By Location:
<select name="lid" size="1">
<option selected value="">Any Location</option>
<?php
while ($bizpitches = mysql_fetch_array($locations)) {
$lid = $bizpitches['id'];
$lname = htmlspecialchars($bizpitches['stateprovince']);
echo "<option value='$lid'>$lname</option>\n";
}
?>
</select></label><br />
<label><br>By Fund Requirements:&nbsp&nbsp&nbsp
<select name="fid" size="1">
<option selected value="">Any Amount</option>
<?php
while ($bizpitches = mysql_fetch_array($funds)) {
$fid = $bizpitches['id'];
$fname = htmlspecialchars($bizpitches['fundsneeded']);
echo "<option value='$fid'>$fname</option>\n";
}
?>
</select></label><br />
<label><br>Containing Text:<input type="text" name="searchtext" />
</label><br />
<br>
<input type="submit" value="Search" />
</form>

</TD>


Here is the code for the display page (the page I suspect I am messing up on)

<?php

$dbcnx = @mysql_connect(XXXXX, 'XXXxX', 'XXXXX');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' .
'database server at this time.</p>');
}

if (!@mysql_select_db('bizpitch')) {
exit('<p>Unable to locate the company ' .
'database at this time.</p>');
}

// The basic SELECT statement
$select = 'SELECT DISTINCT id, company';
$from = ' FROM bizpitches';
$where = ' WHERE 1=1';

$cid = $_POST['cid'];
if ($cid != '') { // A company is selected
$where .= " AND company='$cid'";
}

$iid = $_POST['iid'];
if ($iid != '') { // An industry is selected
$from .= ', industry';
$where .= " AND id=industry AND industry='$iid'";
}
$sid = $_POST['sid'];
if ($sid != '') { // A startup status is selected
$from .= ', startup';
$where .= " AND id=startup AND startup='$sid'";
}
$lid = $_POST['lid'];
if ($lid != '') { // A location is selected
$from .= ', stateprovince';
$where .= " AND id=stateprovince AND stateprovince='$lid'";
}
$fid = $_POST['fid'];
if ($fid != '') { // Funding Requiremnts are selected
$from .= ', fundsneeded';
$where .= " AND id=fundsneeded AND fundsneeded='$fid'";
}
$searchtext = $_POST['searchtext'];
if ($searchtext != '') { // Some search text was specified
$where .= " AND pitch LIKE '%$searchtext%'";
}
?>

<table>
<tr><th>Business Pitch Information</th></tr>

<?php
$companies = @mysql_query($select . $from . $where);
if (!$companies) {
echo '</table>';
exit('<p>Error retrieving companies from database!<br />'.
'Error: ' . mysql_error() . '</p>');
}

while ($bizpitches = mysql_fetch_array($companies)) {
echo "<tr valign='top'>\n";
$id = $bizpitches['id'];
$industry = $bizpitches['industry'];
$company = htmlspecialchars($bizpitches['company']);

echo "<td>$company</td>\n";
"<td>$industry</td>\n";

echo "</tr>\n";
}
?>

</table>

</TD>

 

[flubbard]

What is your desired output and what is it doing now?

- flub

 

[New2Biz]

My desired outpost is to list all of the entries that match the search criteria.

Ideally, the output would look something like this. It would have this inormation for each entry that matches the criteria (so for company 1 all of this information will be displayed, and below company 1 would be company 2 with all this information displayed, etc. etc). The values below are the titles of columns in the mysql database table. The table is titled bizpitches.

Company
website
fundsneeded
startup
contact
email
addressone
addresstwo
city
stateprovince
postalcode
country
pitch
addlinfo
addlinfotwo

 

[New2Biz]

The current result can be seen when you hit the submit button at

http://www.bizpitches.com/bizpitches.php

 

[towerbase]

Can you show us the actual SQL statement that is failing?

I don't mean the PHP statement but the actual string that is passed to the mysql_query that is returning an error.

I haven't gone through your code in any detail but I suspect that your code might be building up an SQL statement containing a where clause such as

WHERE 1=1 AND id=industry AND industry='xxx' AND id=startup AND startup='yyy' ...

Clearly id cannot be equal to industry and equal to startup at the same time so that query will never return any records ever.





Andrew
Hampshire, UK

 

[New2Biz]

That's just it... I am not sure where it is failing. I like the format of the search criteria page

http://www.bizpitches.com/bizpitches.php

but it is not bring back what I would like, and if I use criteria other than company it has errors.

 

[flubbard]

If possible, can you echo out the sql statement once it is constructed and display that for us? Something like

echo ($select . $from . $where);

It should produce a statement similar to

select * from table_name where ...

Have you verified yet that you are getting data back from the search? If not, we should verify that first before determining why it isn't displaying.

- flub

 

[New2Biz]

It is pulling some info back.

 

[towerbase]

What info?

What error messages?

What is the SQL statement you are passing to mysql_query()?

If you do not answer the above questions then we cannot help you.

Andrew
Hampshire, UK

 

[New2Biz]

When I dont filter any of the search criteria it is bringing back the company names, but I cant get it to display any information in the table other than the field "COMPANY". In fact I want all the fields displayed for each company.

When I filter on the search criteria I get messages that read,

Error retrieving companies from database!
Error: Table 'bizpitch.industry' doesn't exist

I dont know php well enough to identify what in my code I am missing to
1) get the search criteria fields to work
2) to have it display all of the information for each company. Ideally, the second page will display multiple fields for each company in the database.

 

[towerbase]

What do you think the message

Error: Table 'bizpitch.industry' doesn't exist

is telling you?


Andrew
Hampshire, UK

 

[flubbard]

It would be helpful to see the sql statement that you are passing to mysql_query. Also, at the mysql prompt do a

show tables;

I would guess either a misspelling in the database or in your code if you can't find the table. We really will need this information to be able to help.

- flub