CODEWITHSUNDEEP
phpsql-server
Kristi Simonson
Kristi Simonson

Reputation: 515

Referencing PHP array from MSSQL

Using MSSQL and PHP, I'm writing a calendar form that lists times in a user's schedule and lets him enter whether he is busy, available, etc.

Once the user submits the info, it's saved to a database.

If the user goes back to the form, he should see the form fields defaulting to the values he already entered.

Ok, so I could query the database for each day and timeslot, and use the result to determine what's shown on the form...but that's 145 calls to the database, since that's the number of timeslots the user can have in a week.

It seems there should be a way to query the database once, store the 145 results in an array, then query the array by day and time for each field.

So I've got:

$sql="SELECT * FROM committee_employee_schedules WHERE fac_id = $user_id";
$result= mssql_query($sql,$conn);

But from there I don't want to go into a while loop with mssql_fetch_array()...how would I go about querying my result set instead? Thanks.

Here's some more example code, since I seem to be failing to communicate:

<form>
<label for="7:30AM">7:30AM</label>
<select name="7:30AM">
<option>Available</option>
<option>Class</option>
<option>Office Hours</option>
</select>

<label for="8:00AM">8:00AM</label>
<select name="8:00AM">
<option>Available</option>
<option>Class</option>
<option>Office Hours</option>
</select>

<label for="8:30AM">8:30AM</label>
<select name="8:30AM">
<option>Available</option>
<option>Class</option>
<option>Office Hours</option>
</select>
</form>

...and this goes up till 9:30PM, Monday-Friday, for a total of 145 dropdown fields.

Each field needs to know IF a row in the table exists for that timeslot, and, if so, select the appropriate activity.

I can grab all the records for the user using the code above, but do I then have to loop over those 145 rows for every single field on the form? Isn't there a way to stick the records into an array and reference it with results['Monday']['7:30'][Activity] or something?

Upvotes: 0

Views: 1371

Answers (1)

Jeff Cashion PhD
Jeff Cashion PhD

Reputation: 674

So, you are getting back 145 records from the database. The results of your SELECT query will be stored in an array, and you will have to iterate through them using a loop. This loop could read the day and timeslot from the record, and store it into an appropriate array. Later, you could then reference that new array in the way you want. I'll try to put together an approximate example below.

<?php
    slotData = array();//Store your results here the way you want to, see down below

    $sql="SELECT * FROM committee_employee_schedules WHERE fac_id = $user_id";
    $result= mssql_query($sql,$conn);
    while($row = mssql_fetch_row($result)) {
        $day = $row['day'];//Guessing your column name here
        $time = $row['time_of_day'];//again, guessing your time here
        $slotData[$day][$time] = $row;
        //this all assumes "$day" looks like "monday", "tuesday", etc.
        //also assumes "$time" looks like "8:00AM", "9:30PM", etc.
    }

    //now that data is in array the way you want, reference it later
    //Assume you are currently trying to populate Monday at 8am position in calendar...
    $curDay = 'monday';
    $curTime = '8:00AM';
    $data = $slotData[$curDay][$curTime];
    //Yeay! "$data" now has all of the information for Monday at 8am.
?>

The bottom line is, iterate through your database result set and move the data into an appropriate slot of a different array, depending on where it should go. Then later, reference that different array however you like without worrying about iterating through ALL of the original records each time. Solved.

Upvotes: 1

Related Questions