Referencing PHP array from MSSQL

Question

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?

Isn't there a more efficient way than looping through 145 results 145 times, checking to see if the row matches my day and time criteria, and then grabbing its value? — Kristi Simonson
– Kristi Simonson, Commented Mar 28, 2013 at 19:57
As an aside, you should try to avoid SELECT *, specify the field list instead. This will help performance and future maintenance of your code. — Hannah Vernon
– Hannah Vernon, Commented Mar 28, 2013 at 19:58
Why would you have to loop 145 times? Wouldn't you just loop once through all records and do whatever you need to with each? How exactly do you want to display the results? — Bafsky
– Bafsky, Commented Mar 28, 2013 at 20:03
I have 145 select dropdown fields in this form, one for each timeslot. The form is not built by looping through the database table. But it references the table. IF the user has saved a record for that timeslot, I need to grab his availability. If I loop through each of 145 table rows to find the one I want, that seems like it would take a lot of resources. — Kristi Simonson
– Kristi Simonson, Commented Mar 28, 2013 at 21:52

Jeff Cashion PhD · Accepted Answer · 2013-03-29 13:59:33Z

1

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.

edited Mar 29, 2013 at 13:59

answered Mar 28, 2013 at 20:05

Jeff Cashion PhD

6396 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kristi Simonson Over a year ago

This worked, except it returned nothing when I used mssql_fetch_row. It works with mssql_fetch_array...not sure why the difference. Thank you!

Jeff Cashion PhD Over a year ago

I only use mysql, so I am not well versed in mssql. Glad I could help and that you recognized that small change.

Collectives™ on Stack Overflow

Referencing PHP array from MSSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related