How To Auto Generate Prepared Statements Based On Array Values?

Drummin · April 20, 2023, 3:21pm

NOTE: I guess you replied while I was typing this.
Yes, you print your arrays you will see that there are 8 keys 0 through 7.

echo "<pre>";
print_r(${$table});	
echo "</pre>";

Array
(
    [0] => id
    [1] => date_and_time
    [2] => domain
    [3] => url
    [4] => title
    [5] => header
    [6] => meta_keyword
    [7] => meta_description
)

So yes there would be an error trying to add extra keys by adding this line after the for loop` for example.

$sql .= " OR $col_[$i] = ?";

The same would apply to your switch.
If you are unsure of value in any location you could always echo it for a test.

echo $i;

BTW. Did you get the file I sent in a PM message?

Drummin · April 20, 2023, 4:49pm

As you can see in rpkamp’s code in POST #5 he has defined both your tables into an array called $tables and you will notice the the primary array key IS the “table name” i.e. 'links_crawls' and 'links_submits'.
In the example $table is defined as 'links_crawls' so combining $tables[$table] you would be looking at the array or values found under this array[key] .
Note: This is a better way to define your table arrays.

idrisalisylbd · April 20, 2023, 4:52pm

@Drummin

Since I am back at my pc tonight and not checking your codes on my phone like 5 days back, I checked your img. On the meta keywords col, you input 2 words: “mobile” and “phone”.
Originally, few mnths back, I intended to do things like this:

Note the 2 keywords in the first row and the 3 keywords in the second row under the meta keywords column.

Q1. I have not tested your code yet and gonna do now. But is it based on a single cell containing more than one keyword ? (More than one keyword in a single row under a single column) ?
Anyway, some programmer somewhere advised me few months ago, (while I was working on another project) to only input 1 keyword per row. And so, I quit that idea. And stuck to his advice. He meant to do things like this, I think, so mysql yields results faster on the keyword searches:

Anyway, this time round, in this particular project of mine, I am having separate cols for each keyword.
Hence, keyword_1, keyword_1_point, keyword_2, keyword_2_point and so on.
If your code was based on more than one keyword per cell then still do not delete your code from above because one day I was gonna revert to that method. But not now. In futurue if possible when I go playing with both methods to see which method yields keyword search results faster when millions of people use my search feature simultaneously.
Anyway, for now, got to stick to the second method. Cols like so:

**url | meta keyword 1 | meta keyword 1 point |

meta keyword 2 | meta keyword 2 point |

meta keyword 3 | meta keyword 3 point |

And so on.

Anyway, I am going through your code now and gonna start bugging you soon as you have gathered I am not a proper intermediate level student. Learning programming at home all by myself. Not got any real programming background. That is why after 5yrs, still at beginner level.

idrisalisylbd · April 20, 2023, 5:05pm

Sorry, I missed this last part on my first read. Just spotted it now.
Was not aware of your PM as tonight for first time sitting on my pc desk since last thursday.
Electricity has blacked out halfen hour ago here after a heavy storm. Finding difficult to type in the dark and my volt stabiliser/controller might run out of charge any minute that is powering the laptop. Unfortunately, my laptop battery is dead.

Drummin · April 20, 2023, 5:18pm

Just saying this is a bad idea as there is no way to match which keyword matched in a search and you were trying to count values based on a keyword match. Going back to that “Car Color” example I mentioned, you wouldn’t have fields color1, color2 in the cars table. You would have another table called car_colors that would have the car ID and the color as fields. The same is going to apply to your “phones” records. All singular attributes can be listed in fields in the “phone” table, but if there are multiple attributes of the same type or name, e.g. keywords they should be in their own table e.g. phone_keywords and so a search can be done on a specific field i.e keyword which then matches a specific phone ID… Just to tie these examples together, You COULD have a table called phone_colors and again would return specific phone ID records for the phones matching the color searched for.

Yes, in this thread we have searched through a string of words using LIKE and found matches to a specific record. But again this is a match of a single field to a record.
It comes down to a search and finding a record that specifically has a keyword or that might have a keyword listed in a string of text.

rpkamp · April 20, 2023, 5:54pm

I would use different words, but basically that’s what happens, yes.

That is correct. [] is the same as array(), just shorter. It exists as of PHP 5.6.