Prepare multidimensional array from PHP using jQuery autocomplete

Beginners - Learning PHP
#1

I have a table in MySQL with 17columns.

CREATE TABLE `MY_COMPONENTLIST` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `quoteDes` varchar(255) DEFAULT NULL,
  `units` varchar(255) DEFAULT NULL,
  `labCost` varchar(255) DEFAULT NULL,
  `labMark` varchar(255) DEFAULT NULL,
  `matCostTotal` varchar(255) DEFAULT NULL,
  `matMarkTotal` varchar(255) DEFAULT NULL,
  `matDes1` varchar(255) DEFAULT NULL,
  `matCost1` int(255) DEFAULT NULL,
  `matDes2` varchar(255) DEFAULT NULL,
  `matCost2` int(255) DEFAULT NULL,
  `matDes3` varchar(255) DEFAULT NULL,
  `matCost3` int(255) DEFAULT NULL,
  `matDes4` varchar(255) DEFAULT NULL,
  `matCost4` int(255) DEFAULT NULL,
  `matDes5` varchar(255) DEFAULT NULL,
  `matCost5` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=304 DEFAULT CHARSET=utf8

I’m using jQuery autocomplete to search through columns then select a value and output it in 3 fields.

The jQuery file is:

$(document).on('keydown', '.codeANCILLARY', function () {

        var id = this.id;
        var splitid = id.split('_');
        var count = splitid[1];

        $('#' + id).autocomplete({
            source: function (request, response) {
                $.ajax({
                    url: "../../MY_PHP_PAGE",
                    type: 'post',
                    dataType: "json",
                    data: {
                        search: request.term,
                        request: 1
                    },
                    success: function (data) {
                        response(data);
                    }
                });
            },
            select: function (event, ui) {
                $(this).val(ui.item.label);
                var id = ui.item.value;

                // AJAX
                $.ajax({
                    url: '../../MY_PHP_PAGE',
                    type: 'post',
                    data: {
                        id: id,
                        request: 2
                    },
                    dataType: 'json',
                    success: function (takesAnyVaribale) {
                        var len = takesAnyVaribale.length;
                        if (len > 0) {
                            var codeANCILLARY = takesAnyVaribale[0]['codeANCILLARY'];
                            var mat50ANCILLARY = takesAnyVaribale[0]['mat50ANCILLARY'];
                            var unitsANCILLARY = takesAnyVaribale[0]['unitsANCILLARY'];
                          
                            $('#codeANCILLARY_' + count).val(codeANCILLARY);
                            $('#mat50ANCILLARY_' + count).val(mat50ANCILLARY);
                            $('#unitsANCILLARY_' + count).val(unitsANCILLARY);
                        }
                    }
                });
                return false;
            }
        });
    });

"MY_PHP_PAGE"

include "config.php";
$request = $_POST['request'];

if ($request == 1) {
    $search = $_POST['search'];
    $query1 = "SELECT * FROM MY_COMPONENTLIST WHERE matDes1 like'%".$search."%'";
    $query2 = "SELECT * FROM MY_COMPONENTLIST WHERE matDes2 like'%".$search."%'";
    $query3 = "SELECT * FROM MY_COMPONENTLIST WHERE matDes3 like'%".$search."%'";
    $query4 = "SELECT * FROM MY_COMPONENTLIST WHERE matDes4 like'%".$search."%'";
    $query5 = "SELECT * FROM MY_COMPONENTLIST WHERE matDes5 like'%".$search."%'";
    $result1 = mysqli_query($con, $query1);
    $result2 = mysqli_query($con, $query2);
    $result3 = mysqli_query($con, $query3);
    $result4 = mysqli_query($con, $query4);
    $result5 = mysqli_query($con, $query5);
    if ($result1 || $result2 || $result3 || $result4 || $result5) {
        while ($row = mysqli_fetch_array($result1)) {
            $response[] = array("value"=>$row['id'],"label"=>$row['matDes1']);
        }
        while ($row = mysqli_fetch_array($result2)) {
            $response[] = array("value"=>$row['id'],"label"=>$row['matDes2']);
        }
        while ($row = mysqli_fetch_array($result3)) {
            $response[] = array("value"=>$row['id'],"label"=>$row['matDes3']);
        }
        while ($row = mysqli_fetch_array($result4)) {
            $response[] = array("value"=>$row['id'],"label"=>$row['matDes4']);
        }
        while ($row = mysqli_fetch_array($result5)) {
            $response[] = array("value"=>$row['id'],"label"=>$row['matDes5']);
        }
    }
    echo json_encode($response);
    exit;
}

if ($request == 2) {
    $id = $_POST['id'];
    $sql = "SELECT * FROM MY_COMPONENTLIST WHERE id=".$id;
    $result = mysqli_query($con, $sql);
    $AncillaryPricing_arr = array();
    while ($row = mysqli_fetch_array($result)) {
        $id = $row['id'];
        $codeANCILLARY = $row['matDes1'];
        $mat50ANCILLARY = $row['matCost1'];
        $unitsANCILLARY = $row['units'];
        $AncillaryPricing_arr[] = array(
            "id" => $id,
            "codeANCILLARY" => $codeANCILLARY,
            "mat50ANCILLARY" => $mat50ANCILLARY,
            "unitsANCILLARY" => $unitsANCILLARY
        );
    }
    echo json_encode($AncillaryPricing_arr);
    exit;
}

if ($request == 1) is working no problem at all but under if($request == 2) how to add more columns to be able to select from matDes2 matDes3 matDes4 matDes5 matCost2 matCost3 matCost4 matCost5

i can search through them inside if request == 1 but i can not select them (matDes2 matDes3 matDes4 matDes5 matCost2 matCost3 matCost4 matCost5) inside if request == 2

i know that i could change my database table into two table then join them by say an id but this table feeding my quote system and invoice system and also my PO

i tried extending if request == 3 and if request == 4 and so on and inside each replace matDes1 and matCost1 to matDes2 and matCost2 and 3 but thats not working too

#3

Well, I am not sure what you are asking. First, your table is oddly formed. Normally for a quoting system,
you would have one table for materials including the description and the costs. Then, you would have a
second table including the owner’s id and a list of materials selected for the quote. In code, you would
select the id of the quote, then run a second query to acquire the list of materials inside the quote.
Your table locks you into 5 items max and each has a separate entry for des and cost. Odd way to handle
it. Is this for a classroom example?
Now, for searching, normally, you would “build” a query using PHP that would search for the items you
entered. In other words, you would NOT lock the search into a set one. In other words you would create
a query loosely like this:
SELECT * FROM MY_COMPONENTLIST WHERE matDes1 LIKE ‘%xxx%’ AND matDes2 LIKE ‘%YYY%’
You can also use OR instead of AND to make the guessing more wide-spread. The logic in a multiple
WHERE condition is different depending on what you need to do. If you are looking for materials that
are listed as five different options, then you need five different queries. If you just want a full list of the
materials that match all of your possible search options, then one query works fine.
So, the search is for you to select materials from a search list, correct? And, you want to be able to
search for five different materials, correct? If true, then one query is all you need. If you want to have
up to five materials listed, then each can be a separate query, but, still not the way it is done normally.
Normally, you would use an “add” button to add the second material, etc.
Perhaps you should give us a little more info on how your site works.

#4

Anytime you have consecutive numbering that is a big red flag your DB design is wrong. Learn about “Database Normalization” and then fix your DB.

Sponsor our Newsletter | Privacy Policy | Terms of Service