PHP/MySQL Questions (Mutiple Tables, Converting Values, etc)

Beginners - Learning PHP
#1

Hello all. First, thanks so much for having a beginner’s forum. That’s me! :slight_smile: I am very comfortable in HTML/CSS, and have recently taken the plunge into PHP/MySQL. I’ve watched video tutorials, read documentation, and searched Google for various bits of assistance along the way. I’m having lots of fun with it, but for my first “work” project, I’ve got to move beyond just having fun and actually apply it to a new site.

With that in mind, I’ll likely be presenting multiple threads here over the next several weeks as we move closer to our launch date, and as I run into roadblocks. I’m the type that does a lot of troubleshooting and research on my own when I first run into an issue, particularly if it’s an explicit error. Most of my inquries here will be advice on techniques and how to best accomplish something, with what limited knowledge I have.

So here’s my first one. I have a site that currently contains two tables. One table contains the information about a location, the other contains information on the open employment positions (140 possible columns). Here is an example record for each table:

[code]FACILITY_INFO
facility_ID = 1
facility_name = Joe’s Computers
facility_city = Orlando
facility_state = Florida
facility_zip = 12345
facility_contact_name = Joe Johnson
facility_contact_phone = 123-444-5678
facility_contact_email = [email protected]
facility_website = www.joecomp.com
etc, etc.

OPEN_POSITIONS
facility_ID = 1
ComputerRepair = YES
Cashier = NO
StockMan = YES
etc., etc.[/code]

With these tables, I will create the following pages:

[ol][li]List all locations in database, sorted by facility name - COMPLETED
List all locations in a particular state (passed by URL), with a list of any open positions at that location as an

    underneath. - MISSING OPEN POSITIONS
    List a particular position name (passed by URL), with a list of any locations that have an opening in that position, grouped and ordered by state - NOT STARTED[/li]
    [li][/li][/ol]

    EXAMPLE: #2

    [code]

    Florida

    Joe's Computers

  • Computer Repair
  • Stock Manager

    • Bob's Widgets

  • Computer Repair
    • [/code]

    EXAMPLE: #3

    [code]

    Computer Repair

    Florida

  • Joe's Computers
  • Bob's Widgets
    • [/code]

    I hope that helps visualize what I’m trying to accomplish. The roadblocks I’m currently up against are getting the open positions to show for #2 above, and needing to know how to approach #3 with all the sorting that is involved.

    For #2, I have the value for the open positions showing (YES), but I don’t know the best way to show the position name instead of the value. The reason I structured the table this way is because the client would like to have an interface where they can use checkboxes to toggle the open positions on and off. I also need a way to convert the column name, i.e. “StockMan” to a full name like “Stock Manager”. Perhaps the way I have structured the table isn’t best for this. There are 140 options in the checkbox list, so I’m feeling a bit overwhelmed with variables. I’m doing some additional research to better understand arrays, as I think that may help here.

    Can any of you point me in the right direction for any of the issues above? Also, based on the 3 main page types you know I’m creating, any suggestions or thoughts not related to my specific questions would be appreciated as well.

    I look forward to hearing from you.

#2

I see that my list did not display the way I wanted. It should read:

With these tables, I will create the following pages:

[ol][li]List all locations in database, sorted by facility name - COMPLETED[/li]
[li]List all locations in a particular state (passed by URL), with a list of any open positions at that location as an

    underneath. - MISSING OPEN POSITIONS[/li]
    [li]List a particular position name (passed by URL), with a list of any locations that have an opening in that position, grouped and ordered by state - NOT STARTED[/li][/ol]

    Sorry about that.

#3

For #2, you can do sql query like this:

[php]$sql = “select FACILITY_INFO.facility_ID as fID, facility_name, facility_city, facility_state, facility_zip, OPEN_POSITIONS.* from FACILITY_INFO left join OPEN_POSITIONS on FACILITY_INFO.facility_ID=OPEN_POSITIONS.facility_ID where facility_state=’”.addslashes($state)."’ order by facility_name, fID";
$r = mysql_query($sql);[/php]
Then, within the loop you can compare previous facility_ID with current facility_ID and output facility info before list of open positions for each facility
[php]<?php
$prev_id=0;
for($i=0;$i<mysql_num_rows($r);$i++){ // loop for each open position
$f=mysql_fetch_array($r);
if($prev_id!=$f[“facility_ID”]){ // display facility info
if($prev_id) echo ‘’; // close list of open pos. for previous facility
echo ‘

’.htmlspecialchars($f[“facility_name”]).’

’;
echo ‘
    ’;
    $prev_id=$f[“facility_ID”];
    }

    // display open position
    echo ‘

  • ’.htmlspecialchars($f[“ComputerRepair”]).’
    • ’;

    }
    echo ‘

’;
?>[/php]
#4

Thanks. I like what I see here. I’m getting the num_rows error when I try to include it on my page:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in…

Any ideas? I’m gonna keep troubleshooting it.

Also, I see that you have “ComputerRepair” listed in the loop. Does that mean I will need to add an echo line for all 140 potential positions?

#5

Fixed the num_row. I had forgotten the case sensitive table names (mine are actually lowercase).

Is there a way to edit posts here, or is it proper to post multiple replies? :slight_smile:

#6

OK, I’ve modified your code a bit, as you’ll see below, and it’s definitely doing what it should, so first off, thanks!

As an update, I have changed the open_positions table from containing YES/NO values to Friendly Name/NONE values. This saves me from having to change the results from saying YES to simply displaying the friendly value I want.

So my question now is what’s the best way to perform the action under //display open position for all 140 potential options.

[php]
$query = “select facility_info.facility_ID as fID, facility_name, facility_city, facility_state, open_positions.* from facility_info left join open_positions on facility_info.facility_ID=open_positions.facility_ID where facility_state=’”.addslashes($state)."’ order by facility_name, fID";
$results = mysql_query($query) or die(mysql_error());

$prev_id=0;
$num=mysql_num_rows($results);

for($i=0;$i<$num;$i++){ // loop for each open position
$f=mysql_fetch_array($results);
if($prev_id!=$f[“facility_ID”]){ // display facility info
if($prev_id)
echo ‘’; // close list of open pos. for previous facility
echo ‘

’.htmlspecialchars($f[“facility_name”]).’

’;
echo ‘
    ’;
    $prev_id=$f[“facility_ID”];
    } 
// display open position  
if ($f["ComputerRepair"]!=NONE) 
echo '<li>'.htmlspecialchars($f["ComputerRepair"]).'</li>';

}
echo ‘’;
[/php]

And I believe that you’ve also helped me begin working on #3 with this code as well. See 2nd code block below.

You’ll notice that I added to the first IF statement so it checks for NONE in the ComputerRepair column. If it finds a value other than NONE, the facility_name and position list will be displayed. This is good, because I only want facilities displayed which have open positions for this view.

What do you think of the code below?

[php]
$position= $_GET[“position”];
echo “

$position

”;
echo “

The following locations have openings that match the position you are seeking.

”;

$query = “select facility_info.facility_ID as fID, facility_name, facility_city, facility_state, open_positions.* from facility_info left join open_positions on facility_info.facility_ID=open_positions.facility_ID order by facility_name, fID”;
$results = mysql_query($query) or die(mysql_error());

$prev_id=0;
$num=mysql_num_rows($results);

for($i=0;$i<$num;$i++){ // loop for each open position
$f=mysql_fetch_array($results);
if($prev_id!=$f[“facility_ID”] && $f[$position]!=NONE){ // display facility info
if($prev_id)
echo ‘’;
echo ‘

’.htmlspecialchars($f[“facility_name”]).’

’;
echo htmlspecialchars($f[“facility_city”]);
$prev_id=$f[“facility_ID”];
}

}
echo ‘’;
[/php]

I can’t seem to get it to work with the echo ‘’ removed, even though I’m not using the

    in this example. I also tried turning the facility names into hyperlinks to the detail page of that facility (using the facility_id), but nothing I tried would work. Not sure why that is, maybe I’m just tired. Gonna call it a day, but will be back at it later. Thanks again!
#7

OK, based on some advice from another forum, I’ve changed the way my tables are constructed. As a result, I think I’ve broken your loop, and I can’t figure out how to fix it.

$state= $_GET[“state”];
print “

$state

”;

[code]$query = “SELECT facility_info.facility_ID as fID, facility_name, facility_city, facility_state, open_positions.position, open_positions.status FROM facility_info LEFT JOIN open_positions ON facility_info.facility_ID=open_positions.facility_ID WHERE facility_state=’”.addslashes($state)."’ ORDER BY facility_info.facility_name, fID";

$results = mysql_query($query) or die(mysql_error());
$prev_id=0;
$num=mysql_num_rows($results);

for($i=0;$i<$num;$i++){ // loop for each open position
$f=mysql_fetch_array($results);
if($prev_id!=$f[“facility_ID”]){ // display facility info
if($prev_id)
echo ‘’; // close list of open pos. for previous facility
echo ‘

’.htmlspecialchars($f[“facility_name”]).’

’;
echo ‘
    ’;
    $prev_id=$f[“facility_ID”];
    } 
// display open position  
if ($f["status"]!=0)
	echo '<li>'.htmlspecialchars($f["position"]).'</li>';

}
echo ‘’;[/code]

The source view of the resulting output is:

<h1>Florida</h1>
<li>ComputerRepair</li>
<li>StockMan</li>
<li>StockMan</li>
<li>ComputerRepair</li></ul>

The correct view should be, based on information entered into the table:

[code]

Florida

Bob's Widgets

  • ComputerRepair
  • StockMan

Carl's Repair Shop

  • StockMan

Joe's Computers

  • ComputerRepair
    • [/code]

    I’m not getting the facility name to show up now. Just as an aside, the new table structure leaves the original FACILITY_INFO table intact, but changed the OPEN_POSITIONS table to be a list of positions as rows instead of columns.

    OPEN_POSITIONS
facility_id  |  position  |  status (1,0)
1  |  ComputerRepair  |  1
1  |  StockMan  |  1
2  |  StockMan  |  1
7  |  ComputerRepair  |  1

    I’m not sure that I’m going to use the status field, but someone suggested that I have it, so I’ve included it. Help?

    #8

    I think you need to change this:[php]if($prev_id!=$f[“facility_ID”]){[/php]
    to this:[php]if($prev_id!=$f[“fID”]){[/php]

    #9

    Ah yes, you’re exactly right. I corrected.

    Now, I have a third table that contains the “Display Name” along with the “Short Name.” I had planned to use this as part of the join.

    Display Name = Computer Repair, Stock Manager, etc.
    Short Name = ComputerRepair, StockMan, etc.

    Currently, the Short Name is what is being printed to the final page. I’d like to modify this so that it checks to find a match between the value in the URL (Short Name) and the Short Name in my SPECIALTY_LIST table. When it does, it will display the Display Name instead of the Short Name.

    I had this working on another page, but when I try to update the Query on this page, it breaks what is happening, and simply repeats the first facility in the list (fID #1) over and over again.

    Sponsor our Newsletter | Privacy Policy | Terms of Service