Database Schema design review (mysql or mongodb)

I am designing DB schema for a project. I have 3 entities:

• professionals
• services
• locations

One professional could provide multiple services (~5) at multiple locations (~1000). One location could have thousands of professionals providing same service and vice-versa.

The queries will be of the form:

1. Find all professionals providing service id-s1 in location id-l1. Sort by popularity/ratings of the professional.
2. Find all locations where a professionalid-p1 provides a service (id-s2).

Caveats - locations also have an associated lat/lng, apart from a standard id. If there are no direct matches for a service in location l1, we may need to search the professionals within radius r from the lat/lng taken given location.

I need to understand two things.

1. Which Database system is better. A relational Mysql or a No-SQL MongoDb (and briefly why).
2. Validation of the schema given below (and suggestions for improvements).

Mysql

professionals | id | name
-----------------------------
                1  | alex
                2  | bob
                3  | charles

services      | id | name
-----------------------------
                1  | stenography
                2  | underwriting
                3  | insurance

locations     | id | lat       | lng         | name
--------------------------------------------------------
                1  | 38.362031 | -98.477500   | office1
                2  | 39.362031 | -99.477500   | office2
                3  | 40.362031 | -100.477500  | office3
                4  | 41.362031 | -101.477500  | office4
                5  | 42.362031 | -102.477500  | office5


services-prof | id | prof_fk | services_fk
--------------------------------------------
                1  | 1       | 1
                2  | 1       | 2
                3  | 1       | 3
                1  | 2       | 1
                2  | 2       | 7
                3  | 3       | 1

location-serv-prof | id | prof_fk | services_fk | location_fk
---------------------------------------------------------------
                      1 | 1       | 1           | 1
                      2 | 2       | 1           | 1
                      3 | 3       | 1           | 1
                      4 | 4       | 1           | 1
                      5 | 5       | 1           | 1

MongoDb

{
    "_id": "t356ah7q",
    "first_name": "Alex",
    "last_name": "Johnson",
    "addresses": {
        "primary_address": {
            "_id": "5765675",
            "lat": "38.362031",
            "lng": "-98.477500"
        },
        "other_addresses": [
            {
                "_id": "5765675",
                "lat": "38.362031",
                "lng": "-98.477500"
            },
            {
                "_id": "5765675",
                "lat": "38.362031",
                "lng": "-98.477500"
            }
        ]
    },
    "services": [
        {
            "service_id": "stenographer",
            "locations": [
                "loc1",
                "loc2",
                "loc3"
            ]
        },
        {
            "service_id": "underwriting",
            "locations": [
                "loc5",
                "loc6",
                "loc7"
            ]
        }
    ]
}

via Chebli Mohamed

How to convert strings from Cyrillic_General_CI_AS to utf8_general_ci in PHP

I need to import some data from MSSQL to MYSQL and I do this programmatically using PHP and PDO. The final lines of code that fetch one last field to be inserted to MYSQL look like this:

$s = $db->prepare("SELECT id FROM object_70912_ 
                         WHERE attr_76453_ = :num AND attr_70954_ = :attr_70954_");
$s->bindParam(':num', $row['building'], PDO::PARAM_STR); 
$s->bindParam(':attr_70954_', $row2['id'], PDO::PARAM_INT);

In this code $row['building'] comes from MSSQL and have this character set - Cyrillic_General_CI_AS, whereas attr_76453_ has this character set in MYSQL - utf8_general_ci. As a result, parameter binding does not work - I can not find appropriate values and this SELECT query allways returns an empty result set - and it is not right, since I checked it. So, I guess that to solve the problem I need to somehow convert data from Cyrillic_General_CI_AS to utf8_general_ci (or it may sound different in terms of PHP).

via Chebli Mohamed

mysql start error on Yosemite, but sqlpro is reading a db

I have several websites, which use PHP and MySQL, these run with no problems as does SQL Pro.

However I have just written a PHP script which runs from the command line and connects to a MySQL database, but I get the following error when trying to run it:

PHP Warning:  mysqli_connect(): (HY000/2002): No such file or directory

I tried restarting MySQL (on the command line and through System Prefs), on the command line I get his error:

ERROR! The server quit without updating PID file

I think the problem is I have 2 MySQL servers installed: /usr/local/mysql/ /usr/local/mysql-5.6.24-osx10.8-x86_64/

My question is, am I correct in thinking that Apache is using one version of MySQL, but PHP on the command line is using another?

How do I find which versions are being used by Apache, MySQL Pro, PHP?

via Chebli Mohamed

PHP mysql to mysqli migration issues (custom functions, procedural style)

Goodmorning

I'm planning to migrate a whole application I made from mysql extension to mysqli, due to next PHP version will not support mysql anymore and I don't want to go fool in the last minutes.

At the moment all page have 2 main inclusions: 1) dbdata.inc.php which contains database connection data 2) function.inc.php which contains most used functions

I'd like to mantain the procedural style also using mysqli extension, but I read that all mysqli functions must receive the connection link as a parameter.

I'm asking for suggestion on the best way (i.e. the most painless solution) to migrate without going mad and without radically rewrite all my php pages.

Actual content of dbdata.inc.php:

$yare_db = mysql_connect($yaredb_host,$yaredb_user,$yaredb_pass) or die("some error warning<br>"); 
mysql_select_db($yaredb_conn); 
mysql_query("SET NAMES 'utf8'");

Most used functions defined in functions.inc.php:

function YQUERY($query) {
    $res = mysql_query($query) or die(mysql_error());
    return $res;
}

function FETCHA($res) {
    $rs = mysql_fetch_array($res);
    return $rs;
}

function NUMROWS($res) {
    $num = mysql_num_rows($res);
    return $num;
}

function AFFROWS() {
    $num = mysql_affected_rows();
    return $num;
}

/* an important function filtering user input texts before passing them to queries */

function msg_safe($string) {
    // some regex and \n to "<br>" substitutions...
    $string = mysql_real_escape($string);
    return $string;
}

Well, the question now is how to migrate:

1) Should i pass the db connection as a function parameter? I.e. something like:

function YQUERY($link,$query) {
    $res = mysqli_query($link,$query);
    return $res;
}

?

2) Should I, instead, define the db connection (defined into included dbdata.inc.php at page start) as GLOBAL variable, inside the function? I.e. something like:

function YQUERY($query) {
     global $link;
     $res = mysqli_query($link,$query);
     return $res;
}

?

3) Should I (it sounds terrific) explicitly declare a new connection inside any custom function? I.e. something like:

function YQUERY($query) {
     $link = mysqli_connect("host","user","pass","db") or die("Error " . mysqli_error($link)); 
     $res = mysqli_query($link,$query);
     return $res;
}

?

4) Other suggestions?

Thanks in advance

via Chebli Mohamed

update data on mysql using ajax

I'm trying to send the id of an image when a user click on it using ajax. Then in the update.php file I'd like to update the id of the image. I'm new on ajax, so I'm not able to figure out my error.

$(document).ready(function(){
$('.show_people_image .love').on('click', function() {

     var id = $(this).attr('id');
     var idpage = "<?php echo $id ?>";
    var sVar1 = encodeURIComponent(id);
    var sVar2 = encodeURIComponent(idpage);
    var sVar3 = "<?php echo $_SESSION['aaa'] ?>";
    var sVar4 = "<?php echo $_SESSION['bbb'] ?>";

     if (sVar1 == sVar2) {
    $.ajax({
        type: "POST",
        url: "update.php",
        data: {lid:sVar1, ml:sVar3, mem:sVar4},
        success: function(data) {
            alert(data);
            },
        error: function () {
        alert('error');
        }  
     });    
     } 
});
});

The update.php file is:

if (isset($_POST['lid']) AND isset($_POST['ml']) AND isset($_POST['mem'])) {

include 'models/connexion_sql.php';


$loveid = $_POST['lid'];

if ($_POST['ml']) {
$str = $_POST['ml'].','.$loveid;
}
else {
$str = $loveid;
}


$sql = 'UPDATE users SET myl= :myl WHERE email = :email';
$req = $bdd->prepare($sql);
$req->bindParam(':email', $_POST['mem'], PDO::PARAM_STR);
$req->bindParam(':myl', $str, PDO::PARAM_STR);
$req->execute();

echo $str;

}  

If I put an echo in the upload.php file before the prepare($sql) the request is successful, otherwise the request fail.

via Chebli Mohamed

Need help interpreting a crash log from QuincyKit

I recently added QuincyKit (a crash log reporter that sits on top of PLCrashReporter) into an app and I received a crash log that I'm having trouble interpreting.

The crash log seems to be inconsistent - it says it was thread 0 that crashed, but the "Last Exception Backtrace" doesn't match with the thread 0 callstack. The last exception points to a table method, but thread 0 callstack indicates an abort occurred during the Quincy manager initialization.

Moreover, the "Last Exception Backtrace" doesn't seem to make much sense taken on it's own - the "canEditRowAtIndexPath" method doesn't even include a call to "removeObjectAtIndex" at all (see below for the method).

Can anyone shed any light onto whether or not I should be paying attention to the "Last Exception Backtrace" or is that a red herring, and I should really be looking into why PLCrashReporter aborted during start up?

Many thanks

Crash log excerpt:

Exception Type:  SIGABRT
Exception Codes: #0 at 0x38362df0
Crashed Thread:  0

Application Specific Information:
*** Terminating app due to uncaught exception 'NSRangeException', reason: '*** -[__NSArrayM objectAtIndex:]: index 7 beyond bounds [0 .. 6]'

Last Exception Backtrace:
0   CoreFoundation                       0x29920fef <redacted> + 126
1   libobjc.A.dylib                      0x37d0cc8b objc_exception_throw + 38
2   CoreFoundation                       0x29833821 -[__NSArrayM removeObjectAtIndex:] + 0
3   DART                                 0x000906b3 -[DeliveryComplete tableView:canEditRowAtIndexPath:] + 262
4   UIKit                                0x2d0a3c25 -[UITableView _canEditRowAtIndexPath:] + 60
5   UIKit                                0x2d0a3a7f -[UITableView _setupCell:forEditing:atIndexPath:animated:updateSeparators:] + 130
6   UIKit                                0x2d0a1179 <redacted> + 2320
7   UIKit                                0x2cf82a31 +[UIView performWithoutAnimation:] + 72
8   UIKit                                0x2d0a0861 -[UITableView _configureCellForDisplay:forIndexPath:] + 336
9   UIKit                                0x2d246383 -[UITableView _createPreparedCellForGlobalRow:withIndexPath:willDisplay:] + 498
10  UIKit                                0x2d24642f -[UITableView _createPreparedCellForGlobalRow:willDisplay:] + 54
11  UIKit                                0x2d23b013 -[UITableView _updateVisibleCellsNow:isRecursive:] + 2258
12  UIKit                                0x2d049657 -[UITableView layoutSubviews] + 186
13  UIKit                                0x2cf73023 -[UIView layoutSublayersOfLayer:] + 546
14  QuartzCore                           0x2c993d99 -[CALayer layoutSublayers] + 128
15  QuartzCore                           0x2c98f5cd <redacted> + 360
16  QuartzCore                           0x2c98f455 <redacted> + 16
17  QuartzCore                           0x2c98edf1 <redacted> + 224
18  QuartzCore                           0x2c98ebdf <redacted> + 434
19  UIKit                                0x2cf6b23b <redacted> + 126
20  CoreFoundation                       0x298e6fed <redacted> + 20
21  CoreFoundation                       0x298e46ab <redacted> + 278
22  CoreFoundation                       0x298e4ab3 <redacted> + 914
23  CoreFoundation                       0x29831201 CFRunLoopRunSpecific + 476
24  CoreFoundation                       0x29831013 CFRunLoopRunInMode + 106
25  GraphicsServices                     0x3100d201 GSEventRunModal + 136
26  UIKit                                0x2cfd5a59 UIApplicationMain + 1440
27  DART                                 0x00015491 _mh_execute_header + 25745
28  libdyld.dylib                        0x38298aaf <redacted> + 2

Thread 0 Crashed:
0   libsystem_kernel.dylib               0x38362df0 __pthread_kill + 8
1   libsystem_c.dylib                    0x382fe909 abort + 76
2   DART                                 0x00122dd7 -[PLCrashReporter enableCrashReporterAndReturnError:] + 1294
3   CoreFoundation                       0x2992131f <redacted> + 630
4   libobjc.A.dylib                      0x37d0cf13 <redacted> + 174
5   libc++abi.dylib                      0x37643de3 <redacted> + 78
6   libc++abi.dylib                      0x376438af __cxa_rethrow + 102
7   libobjc.A.dylib                      0x37d0cdd3 objc_exception_rethrow + 42
8   CoreFoundation                       0x2983129d CFRunLoopRunSpecific + 632
9   CoreFoundation                       0x29831013 CFRunLoopRunInMode + 106
10  GraphicsServices                     0x3100d201 GSEventRunModal + 136
11  UIKit                                0x2cfd5a59 UIApplicationMain + 1440
12  DART                                 0x00015491 _mh_execute_header + 25745
13  libdyld.dylib                        0x38298aaf <redacted> + 2

"canEditRowAtIndexPath" method:

-(BOOL) tableView:(UITableView *)tableView canEditRowAtIndexPath:(NSIndexPath *)indexPath
{
    if (tableView.tag == SIGNER_TABLE_TAG_VALUE)
    {

        RouteStopData *currentStop = [CurrentRoute singleton].selectedStop;
        NSArray *signers = [currentStop signerNamesForStop];

        if (indexPath.row >= [signers count])
        {
            return NO;
        }

        if ([[signers objectAtIndex:indexPath.row] isEqualToString:DARK_DROP_SIGNER_STRING] ||
            [[signers objectAtIndex:indexPath.row] isEqualToString:PAPER_INVOICE_SIGNER_STRING] ||
            [[signers objectAtIndex:indexPath.row] isEqualToString:ADD_NEW_SIGNER_STRING]
            )
        {
            return NO;
        }

        return YES;
    }

    return NO;
}

StoreKit.framework deleted from Library directory

Is there any change to get StoreKit.framework from iOS SDK 8.3 if I accidentally deleted it (without downloading Xcode again)?

It was linked to one of my projects, at some point I needed to delete it but instead of pressing "Remove reference", I pressed "Move to trash" and also "Emptied trash" after that...so now I'm unable to get it back.

Thanks, Alin