MySQL查询While循环重复获取数据仅m_frame_cat变动问题求助

Hey there, let's break down your problem and fix it step by step. First off: you don't need to switch to a foreach loop—your while loop syntax is totally valid. The issue is almost certainly coming from your SQL query logic, not the loop itself.

Why You're Seeing Duplicate Data #

The "only m_frame_cat changes, other fields repeat" behavior is a classic sign of a Cartesian product (unintended row duplication) from your table join. Here's why:

• You're using the old comma-separated table syntax for joins, and your only link between replacementlenses and lenslist is replacementlenses.fitsbrand = lenslist.brand. If lenslist has multiple rows with the same brand, each row from replacementlenses will be paired with every matching lenslist row. That means you'll get duplicate replacementlenses data, paired with different lenslist fields like m_frame_cat.
• It's also possible you're missing a critical join condition—like matching fitsmodel to lenslist.model—which would narrow down the join to only relevant pairs instead of broad brand matches.

Step-by-Step Fixes #

1. Rewrite Your SQL Query (The Big Fix) #

Switch to explicit JOIN syntax (it's clearer and less error-prone) and add any missing join conditions. Also, use table aliases to avoid field confusion:

$query = "SELECT 
    r.skuid, 
    r.sku, 
    r.title, 
    r.description, 
    r.longdescription, 
    r.pagelink, 
    r.pagelinkrx, 
    r.imagelink, 
    r.imagelinksmall, 
    r.imagelink2, 
    r.imagelink3, 
    r.fitsbrand, 
    r.fitsmodel, 
    r.color, 
    r.colorcode, 
    r.polarized, 
    r.producttype, 
    r.fuselenses, 
    r.lenswidth, 
    r.lensheight, 
    r.frame_material, 
    l.brand AS lens_brand, 
    l.model AS lens_model, 
    l.m_brand_cat, 
    l.m_frame_cat 
FROM replacementlenses r
INNER JOIN lenslist l 
    ON r.fitsbrand = l.brand 
    -- Add this if fitsmodel should match lenslist.model (your note mentions Lucky Fly should be next)
    AND r.fitsmodel = l.model 
WHERE l.brand LIKE '$brand%' 
AND r.colorcode = 'C'";

The key addition here is the AND r.fitsmodel = l.model condition—this ensures you're pairing each replacement lens with the exact matching frame model, not just any frame from the same brand. That should eliminate the duplicate rows.

2. Debug to Confirm the Source #

To double-check if the issue is coming from the database or your loop, add a debug log inside your while loop to inspect each row as it's fetched:

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    // Log the full row to your server error log (or print it to the screen temporarily)
    error_log(print_r($row, true));
    
    $skuid = $row['skuid'];
    $sku = $row['sku'];
    $pagelink = $row['pagelink'];
    $fitsbrand = $row['fitsbrand'];
    $fitsmodel = $row['fitsmodel'];
    $m_brand_cat = $row['m_brand_cat'];
    $m_frame_cat = $row['m_frame_cat'];
    
    // Your existing string processing logic...
}

If the logged rows already show duplicate replacementlenses data, you know the SQL query needs further tweaking. If the rows are correct in the log but still repeat in your output, then you'd look at how you're storing/displaying the data (but that's less likely here).

3. A Note on Foreach (If You Still Want to Use It) #

While it won't fix your duplicate data issue, if you prefer foreach, you can first fetch all rows into an array, then loop through it:

// Fetch all rows first
$allRows = [];
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    $allRows[] = $row;
}

// Now loop with foreach
foreach($allRows as $row) {
    // Same variable assignments and logic as before
}

Again, this doesn't solve the root problem—fixing the SQL query is critical.

Extra Security Tip #

You're directly interpolating $brand into your SQL query, which puts you at risk of SQL injection. Switch to prepared statements to fix this:

// Prepare the statement
$stmt = mysqli_prepare($conn, "SELECT 
    r.skuid, 
    r.sku, 
    -- ... rest of your SELECT fields ...
FROM replacementlenses r
INNER JOIN lenslist l 
    ON r.fitsbrand = l.brand 
    AND r.fitsmodel = l.model 
WHERE l.brand LIKE ? 
AND r.colorcode = 'C'");

// Bind the brand parameter (add % for the LIKE clause)
$likeBrand = $brand . '%';
mysqli_stmt_bind_param($stmt, "s", $likeBrand);

// Execute and get results
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

This is safer and avoids syntax issues if $brand contains special characters.

内容的提问来源于stack exchange，提问作者Brian Pluhar