I need to make a very conditional inquiry, but my knowledge is not enough, I will be very glad if you help

Hello,

I will try to explain my question broadly so that we don’t get misunderstood and waste time.

I need to make a very conditional inquiry, but my knowledge is not enough, I will be very glad if you help.

The code below we created a long time ago with their help in a forum.

$girler = array();
$entries = "9,10";
$girler = explode(",", $entries);
$multiswitch_giris = current($girler);
$entry_stop = end($girler); 
	

$query1 = $connection->prepare("
SELECT DISTINCT
brands.product_brand
FROM multiswitch
JOIN brands ON multiswitch.product_brand = brands.id
ORDER BY brands.product_brand ASC");
$query1->execute();

$piece = $query1->rowCount();
$brandarray = array();
    if ($piece > 0) {
        while ($row = $query1->fetch()){
            $brandarray[] = $row['product_brand'];
        }
    }

$last_entries = array();
if(is_array($girler)){
foreach($girler as $giri)
{
if ($giri >= $multiswitch_giris) {
$last_entries[] = $giri;
}
}
}
unset($girler);

 switch($number_subscribers){ 
case $number_subscribers <= 15 : $target_option= array(
"16", "8-8"); break;
case $number_subscribers <= 20 : $target_option= array(
"20", "12-12"); break;
case $number_subscribers <= 25 : $target_option= array(
"32", "16-16"); break;
case $number_subscribers <= 32 : $target_option= array(
"32", "16-16"); break;
 }

foreach($brandarray as $brand) {

foreach($last_entries as $lastentr) {

   unset($finite_array);
   unset($cascade_array);
   unset($hsecs);
   unset($hsecsay);
   unset($fids);
   unset($ids);

$query11 = $connection->prepare("SELECT 
multiswitch.product_id, 
multiswitch.product_type,
multiswitch.product_input,
multiswitch.product_output, 
multiswitch_type.multi_type,
brands.product_brand 
FROM multiswitch
INNER JOIN multiswitch_type ON multiswitch.product_type = multiswitch_type.id
WHERE multiswitch.product_input=? AND brands.product_brand=? AND multiswitch_type.multi_type=? AND multiswitch.product_stok=?");

$query11->execute([$lastentr, $brand, 'FINITE', '1']);


$query12 = $connection->prepare("SELECT 
multiswitch.product_id,
multiswitch.product_type,
multiswitch.product_input,
multiswitch.product_output,
multiswitch_type.multi_type,
brands.product_brand 
FROM multiswitch
INNER JOIN multiswitch_type ON multiswitch.product_type = multiswitch_type.id
WHERE multiswitch.product_input=? AND brands.product_brand=? AND multiswitch_type.multi_type=? AND multiswitch.product_stok=?");

$query12->execute([$lastentr, $brand, 'CASCADE', '1']);

$piece = $query11->rowCount();
if ($piece > 0) {
  while ($row = $query11->fetch()){
          $fids[] = $row["product_id"];
    $finite_array[] = $row["product_output"];
    
}

}

$piece = $query12->rowCount();
if ($piece > 0) {
  while ($row = $query12->fetch()){
            $ids[] = $row["product_id"];
    $cascade_array[] = $row["product_output"];
   }

}

if (!isset($finite_array) && $lastentr == $entry_stop) {
  show_if_out_of_stock($brand);continue 2;
}
if (!isset($finite_array)) {
  continue;
}

for ($vi = 0; $vi < count($target_option); $vi++) {
$hsecs = explode("-", $target_option[$vi]);
$hsecsay = count($hsecs);

if ($hsecsay == 1){ 
$varsingle = array_search($hsecs[0], $finite_array);
if ($varsingle !== false) {
show_if_the_found_product_is_one( 
 $total_output_found = $hsecs[0],
 $found_brand = $brand, $number_subscribers,
 $found_finite_id = $fids[$varsingle]
 ); 
break 2;
}
}
if (!isset($cascade_array) && $lastentr == $entry_stop) {show_if_out_of_stock($brand);continue 3;}
if (!isset($cascade_array)) {continue 2;}

if ($hsecsay == 2){ 
 $var2finite = array_search($hsecs[0], $finite_array);
$var2cascade = array_search($hsecs[1], $cascade_array);
if (($var2finite !== false) && ($var2cascade !== false)) {
Show_if_more_than_one_product_found( 
 $total_output_found = $hsecs[0]+$hsecs[1],
 $found_brand = $brand, $number_subscribers,
 $found_finite_id = $fids[$var2finite],
 $found_cascade_ids = array($ids[$var2cascade])
 );
	break 2;
}
 $var2finite = array_search($hsecs[1], $finite_array);
$var2cascade = array_search($hsecs[0], $cascade_array);
if (($var2finite !== false) && ($var2cascade !== false)) {
Show_if_more_than_one_product_found( 
 $total_output_found = $hsecs[0]+$hsecs[1],
 $found_brand = $brand, $number_subscribers,
 $found_finite_id = $fids[$var2finite],
 $found_cascade_ids = array($ids[$var2cascade])
 );
	break 2;
}
}

if ($vi == (count($target_option) -1) && $lastentr == $entry_stop) {show_if_out_of_stock($brand);}
}  //for


} // foreach
   
}  // foreach...

function show_if_the_found_product_is_one($total_output_found, $found_brand, $number_subscribers, $found_finite_id) {
// HTML table for output
}     
      
function Show_if_more_than_one_product_found($total_output_found, $found_brand, $number_subscribers, $found_finite_id, $found_cascade_ids) {
// HTML table for output
} 

function show_if_out_of_stock($brand) {
// HTML table for output
}

What kind of product is queried with this code?

Product input options: 5,9,10,13,17

Output options for product subscribers: 8,12,16,20,24,32

Also this product has 2 types: CASCADE and FINITE

CASCADE feature has outputs under as inputs

FINITE feature does not have outputs under

Example:

It is necessary to use two products to achieve 64 subscriber output. The incoming signal is entered into the input of the 32 subscriber CASCADE product. The output below is connected to the input of the FINITE product with 32 subscribers, and we get a Multiswitch with 64 subscriber outputs. So, the desired number of subscribers is reached by connecting serially.

If the desired subscriber is reached with one product with this code, only one FINITE product is given. If the desired subscriber is reached with more than one product, it gives one FINITE product and gives the other products as CASCADE, so the desired subscriber is reached by connecting in series.

As in this screenshot, it lists each brand with a separate table. 105 subscriber system was requested and 108 subscriber outputs were obtained.
kaskadeskisistem
CASCADE serial connection system is not a good system due to the decrease in the signal in case of multiple subscribers. So now it has to have the system as I am trying to do.
Now what are the rules of the query I’m trying to do:

Thanks to the products that have developed over time, the system types have increased.

  1. CASCADE system
  2. TAP OFF system
  3. COMPACT system

    In the picture above, there is a building with 20 floors and 40 apartments. When two cables go to each flat, 80 subscribers are reached. Cables are carried in a shaft.
    On the 18th, 13th, 7th and 3rd floors, there are panels where the cables are collected. There are 15 cables on the 18th floor, 25 on the 13th floor, 13 on the 7th floor, 27 cables on the 3rd floor and this block is fed by an optical cable.

    The two screenshots above are the same as the first screenshot. It just shows that the number of cables in the panels is equal. “20” or “20,20,20,20” means these two are the same.

    In this screenshot, 2 Optics are selected. So it says: I want to use optics in two places on 1 shaft. In other words, optic cable will go to the 18th and 7th floors. In this case, if the CASCADE system is preferred, the Cascade output will go from the 18th floor to the 13th floor and will end here. It will not go to the 7th floor because there is another optical entrance on the 7th floor.

    In this screen shot, 4 optics are selected, which means that even if the CASCADE system is preferred, the signal will not be transmitted between the panels, as optical cables will go to each panel.

    In this screenshot, 3 optical cables are selected and there are 4 panels. The rule here is: fiber optic will go to other panels, excluding the panel with the fewest cables. Optic cable will go to the 18th, 13th and 3rd floors, but the optic cable will not go to the 7th floor, because the number of cables there is the least.

    In this screenshot, 2 shafts are selected. In other words, cables are carried from 2 shafts in the block and all the rules are valid, since there are only 2 shafts, the products will be calculated as x2.
    Note: Blocks can be multiple. The information of the blocks may differ.

Now I want to query like below:

Creating a table with all products for each brand as in the screenshot below.
kaskadeskisistem
For example, if I make a query with existing code:

There are 5 panels in X block. Let’s assume that there are 35, 44, 39, 52, 23 cables from the top floor to the bottom.

Desired system type is COMPACT or TAP OFF

If I query one by one, it will give the following result:

  • 36 subscriber outputs are obtained by giving 10x20 CASCADE + 10x16 FINITE for 35 cables.
  • 44 subscriber outputs are obtained by giving 10x24 CASCADE + 10x20 FINITE for 44 cables.
  • 40 subscriber outputs are obtained by giving 10x20 CASCADE + 10x20 FINITE for 39 cables.
  • 52 subscriber outputs are obtained by giving 10x32 CASCADE + 10x20 FINITE for 52 cables.
  • 24 subscriber outputs are obtained by giving 10x24 FINITE for 23 cables.

I want to query this not individually, but as a whole. Query should be made according to the number of cables in each panel. CASCADE products should be FINITE instead.

Another important issue is that in a multi-block and multi-panel system, we can select all products by enclosing them in a table. However, it cannot be understood which of these products belongs to which block and which product belongs to which panel. To fix this problem, I want to write a file like below in the background.

Note: I need to add previously selected products and mandatory products to the panels without querying.


I want to receive such a file so that it would be clear which products belong to which panel.

If the desired system is CASCADE:

Again, starting from the panel on the top floor, by making a separate query for each panel, it will continue with CASCADE for the next panel and finish by giving one FINITE to the last panel.

If there is more than one optic in the shaft (I explained the optic distribution in pictures 4, 5 and 6), it will finish by giving FINITE on the panel before the optic.

Example resource

$floors_with_boards_in_the_apartment = [
        "1" => "18,13,7,3",
        "2" => "18,13,7,3",
        "3" => "18,13,7,3",
        "4" => "18,13,7,3",
        "5" => "18,13,7,3",
        "6" => "18,13,7,3",
        "7" => "18,13,7,3",
        "8" => "18,13,7,3",
        "9" => "18,13,7,3",
        "10" => "18,13,7,3"
    ];

$number_of_cables_in_panels = [
        "1" => "25,20,15,20",
        "2" => "25,20,15,20",
        "3" => "25,20,15,20",
        "4" => "65,49,80,90",
        "5" => "25,20,15,20",
        "6" => "25,20,15,20",
        "7" => "25,20,15,20",
        "8" => "25,20,15,20",
        "9" => "25,20,15,20",
        "10" => "25,20,15,20"
    ];

$number_of_optics_per_shaft =
    [
        "1" => "4",
        "2" => "1",
        "3" => "4",
        "4" => "4",
        "5" => "3",
        "6" => "4",
        "7" => "4",
        "8" => "2",
        "9" => "4",
        "10" => "4"
    ];

$number_of_shafts_in_the_apartment =
    [
        "1" => "2",
        "2" => "1",
        "3" => "2",
        "4" => "2",
        "5" => "2",
        "6" => "1",
        "7" => "1",
        "8" => "2",
        "9" => "2",
        "10" => "1"
    ];

    $alphabet = ["1"=>"A","2"=>"B","3"=>"C","4"=>"D","5"=>"E","6"=>"F","7"=>"G","8"=>"H","9"=>"I","10"=>"J","11"=>"K","12"=>"L","13"=>"M","14"=>"N","15"=>"O","16"=>"P","17"=>"Q","18"=>"R","19"=>"S","20"=>"T","21"=>"U","22"=>"V","23"=>"W","24"=>"X","25"=>"Y","26"=>"Z"];

Thank you very much in advance

Sample working codes are attached below.
Maybe you can help more easily with sample working codes.
Download sample working code

Thanks

I don’t think you will get too many people willing to try and come up to speed on what the application is doing now in order to provide direction on how to make changes to it.

This code/query(ies) is hard to understand. The code has few/no helpful comments. The variable names don’t indicate the meaning of the data. Database column names don’t indicate the meaning of the data. There’s unnecessary code, queries being executed inside of loops, …

It would be helpful to go through and clean up the existing code as much as possible, so that anyone reading it can understand what it is trying to do. A specific example follows, for the code that’s getting a distinct list (array) of brand names (which should probably be both the brand id and the brand name, as both should be used in the rest of the code) -

// the brand(s) table should have an id and a name column (which is apparently the product_brand column)
// the multiswitch table should have a brand_id column (which is apparently the product_brand column)
// so, the two product_brand columns have completely different meanings and neither indicates the actual meaning of the data

// build the sql query statement(s) in a php variable. this makes debugging easier. it also separates the sql query syntax from the php syntax as much as possible
// name the database connection variable as to what it actually is
// don't use a prepared query if you aren't executing a prepared query. just use the query method
// make use of the PDO fetch methods and fetch modes to fetch data the way that you want

// get the distinct brand names which are in use (there is data in the multiswitch table)
$sql = "SELECT DISTINCT b.name
 FROM brands b
 JOIN multiswitch m ON b.id = m.brand_id
 ORDER BY b.name";
$stmt = $pdo->query($sql);

// this will either be an array of the brand names or an empty array if the query did not match any data
$brand_names = $stmt->fetchAll(PDO::FETCH_COLUMN); // get an array of the 1st/only column of data

For the queries being executed inside the loops, just execute a single query that gets all the data you want in the order that you want it. Then index (pivot) the data when you fetch it, using the brand, switch type (cascade/finite), and whatever $lastentr represents as the array indexes. You can then simply loop over the data to produce the result that you want.

I hope someone helps

I don’t have to use this code.
The important thing is that it searches the priority products that I will determine and gives the result.
Example: Although it is a product with 32, 40 outputs, it searches according to the priority I set “16-16”, “32” ; “20-20”, “40” as

Thank you for more organized code
Since I don’t have much knowledge, I can only write enough to work.

Yes, I don’t know how to do this, can you help with sample code

Thanks

I tried to add some more explanation for your Download Code but I can’t explain more because I don’t fully understand the code.

I ask for your help

Sponsor our Newsletter | Privacy Policy | Terms of Service