display data from database in a specific order: php or mysql?

Question

I have a table for phone numbers:

phone:
phoneID (PK)
peopleID (fK)
countrycode
areacode
phonenumber
extension
phonetype //EDIT!!! (sorry forgot this in first post)

Every person can have max 4 phone numbers: company, fax, home, mobile.

I have an edit form. The edit form pulls data from the DB and populate the form. This code to pull data:

$stmt = $conn->prepare("SELECT * FROM phone WHERE peopleID=?");
if ( !$stmt ) {
    die(printf("Error: %s.\n", mysqli_stmt_error($stmt) ) );
}
else if ( !$stmt->bind_param('i', $peopleID) ) {
    die(printf("Error: %s.\n", mysqli_stmt_error($stmt) ) );
}
else if ( !$stmt->execute() ) { 
    die(printf("Error: %s.\n", mysqli_stmt_error($stmt) ) ); 
} else {
    $resultaddress = $stmt->get_result();

    while($row = $resultaddress->fetch_assoc()) {
        $phoneID_array[] = $row['phoneID'];  
        $phonetype_array[] = (isset ($row['phonetype']) ? $row['phonetype'] : "");
        $countrycode_array[] = (isset ($row['countrycode']) ? $row['countrycode'] : "");
        $areacode_array[] = (isset ($row['areacode']) ? $row['areacode'] : "");
        $phonenumber_array[] = (isset ($row['phonenumber']) ? $row['phonenumber'] : "");
        $extension_array[] = (isset ($row['extension']) ? $row['extension'] : "");
    }   
}

and this to populate the form:

for ($i = 0; $i < 4; $i++) {
    echo 'Phone<input type="text" name="type[]" id="" value="' . (isset ($phonetype_array[$i]) ? $phonetype_array[$i] : "company") . '"/>';
    echo '<input type="text" name="countrycode[]" id="" size="3" maxlength="3" value="' . (isset ($countrycode_array[$i]) ? $countrycode_array[$i] : "") . '"/>';
    echo '<input type="text" name="countrycode[]" id="" value="' . (isset ($areacode_array[$i]) ? $areacode_array[$i] : "") . '"/>';
    echo '<input type="text" name="number[]" id="" value="' . (isset ($phonenumber_array[$i]) ? $phonenumber_array[$i] : "") . '"/>';
    echo '<input type="text" name="extension[]" id="" value="' . (isset ($extension_array[$i]) ? $extension_array[$i] : "") . '"/><br>';                

}

The form now displays:

company 000 000 1234567

home 000 000 1234569

(no empty fields to eventually enter new numbers)

PROBLEM:

I would like to display phone numbers always in the same order: Let's say I only have company and home numbers in DB, the edit form should look like

company : 000 000 1234567

fax : empty fields to fill

home : 000 000 1234569

mobile : empty fields to fill

It could probably be done with associative arrays, but I don't know how :(

Thanks for your help!

EDIT: After reading all the answers and comments here, I understood that it was a better design to have a dedicate table phonetype, and use a foreign key in the phone table. Thinking that this was a completely different scenario, I asked this other question mysql left join not return all left table row Thanks again to everybody who took the time to help me.

Consider using an ORDER BY clause in your query? Also, where are you storing the phone type in the database ? — Maximus2012
– Maximus2012, Commented Jan 20, 2016 at 22:32
@Maximus2012 the issue is probably that two of the records don't exist, so the empty fields always appear last — scrowler
– scrowler, Commented Jan 20, 2016 at 22:33
@RobbieAverill you're right. In that case, the user should probably have a second table for phone type and then try iterating through that table first. — Maximus2012
– Maximus2012, Commented Jan 20, 2016 at 22:37
It's not clear what' do you want because your title asks for something, but then the problem you explain (if I got it well) talks about something else — Gavriel
– Gavriel, Commented Jan 20, 2016 at 22:37
So how are you indicating that one of those rows is a Company or Home phone number? There is nothing in the table to indicate that — RiggsFolly
– RiggsFolly, Commented Jan 20, 2016 at 22:39

Gavriel · Accepted Answer · 2016-01-20 23:07:41Z

1

You'll need to add another field to the phone table:

'phonetype' ENUM('company','fax','home','mobile')

and the query:

SELECT phone.* FROM people 
    LEFT JOIN phone ON phone.peopleID=people.peopleID 
WHERE people.peopleID=? AND phone.phonetype='company'
UNION 
SELECT phone.* FROM people 
    LEFT JOIN phone ON phone.peopleID=people.peopleID 
WHERE people.peopleID=? AND phone.phonetype='fax'
UNION 
SELECT phone.* FROM people 
    LEFT JOIN phone ON phone.peopleID=people.peopleID 
WHERE people.peopleID=? AND phone.phonetype='home'
UNION 
SELECT phone.* FROM people 
    LEFT JOIN phone ON phone.peopleID=people.peopleID 
WHERE people.peopleID=? AND phone.phonetype='mobile'

Now you'll get 4 lines, some of them might be nulls

edited Jan 20, 2016 at 23:07

answered Jan 20, 2016 at 22:43

Gavriel

19.3k13 gold badges73 silver badges116 bronze badges

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

12 Comments

codeispoetry Over a year ago

@Gravel Thanks Gravel, I'm testing it now. 2 Little questions: 1) if I already have the field phonetype, how do I set it as ENUM? 2) Do you think for phone types, it is better to have a separate table? Thanks!

Gavriel Over a year ago

it doesn't matter what type it is. If it's an integer, then instead of phone.phonetype='company' use: phonetype=0, etc.

miken32 Over a year ago

Worth knowing what you're getting into with an ENUM: komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil

codeispoetry Over a year ago

sorry Gavriel, where do I enter 'phonetype' ENUM('company','fax','home','mobile') ? It gives me an error

Gavriel Over a year ago

You don't have to, I wrote that before you remembered you already have that field, though you still hold the secret about it's type. Just use the one you already have.

|

miken32 · Accepted Answer · 2016-01-20 23:57:54Z

1

I could think of a dozen ways to improve this; most obvious would be a separate phone numbers table in the database. But, for easiest drop-in code replacement, you can just make it a single array keyed by type:

/* Make an empty array with empty values for each phone type */
$emptyphone = array(
    "countrycode"=>"",
    "areacode"=>"",
    "phonenumber"=>"",
    "extension"=>"",
);
$phones = array(
    "company"=>$emptyphone + array("phonetype"=>"company"),
    "fax"=>$emptyphone + array("phonetype"=>"fax"),
    "home"=>$emptyphone + array("phonetype"=>"home"),
    "mobile"=>$emptyphone + array("phonetype"=>"mobile"),
);

/* Now fill in the values you do have */
while($row = $resultaddress->fetch_assoc()) {
    $phones[$row["phonetype"]] = $row;
}

/* Now you can loop through them with a simple foreach */
foreach($phones as $phone) {
    echo 'Phone<input type="text" name="type[]" id="" value="' . $phone["phonetype"] . '"/>';
    echo '<input type="text" name="countrycode[]" id="" size="3" maxlength="3" value="' . $phone["countrycode"] . '"/>';
    echo '<input type="text" name="countrycode[]" id="" value="' . $phone["areacode"] . '"/>';
    echo '<input type="text" name="number[]" id="" value="' . $phone["phonenumber"] . '"/>';
    echo '<input type="text" name="extension[]" id="" value="' . $phone["extension"] . '"/><br>';                
}

answered Jan 20, 2016 at 23:57

miken32

42.5k16 gold badges127 silver badges177 bronze badges

3 Comments

codeispoetry Over a year ago

Thanks Miken! What do you mean by separate table for phone numbers? A separate table for every type? Like: table_companyphone, table_homephone... etc?

miken32 Over a year ago

Sorry, meant phone types. Then phonetype in your existing database becomes a foreign key and the join would result in empty values for any types that were missing. Similar to the other answer, but I didn't want to suggest database changes, not sure if this is live code or not.

codeispoetry Over a year ago

Thanks miken, it's not live yet, so I could add a foreign key. I can still add a phonetype table. What would you write the code with the join in that case $stmt = $conn->prepare("NEW SQL WITH JOIN"); . Sorry I'm still new at this!

Collectives™ on Stack Overflow

display data from database in a specific order: php or mysql?

2 Answers 2

12 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related