0

I found this link fetch_assoc() works only once in a loop but not solved my problem I need to fetch 2 diff tables and result the select something to XML sitemap

i need the result to be inside each others:

Code:

<?php

$my_sqli = new mysqli('localhost', 'root', '', 'my_db') or die(mysqli_error($my_sqli));
$data1 = $my_sqli->query("select urls from category_table where locale = 'en' AND NOT( trans_url <=> NULL); ") or die($my_sqli->error);
$data2 = $my_sqli->query("select name from something_table where locale = 'en'") or die($my_sqli->error);

$rows2 = $data2->fetch_all(MYSQLI_ASSOC);
header("Content-Type: application/xml; charset='utf-8'");
$xml_str = '<!--?xml version="1.0" encoding="iso-8859-1"?-->'. PHP_EOL;
$xml_str .= '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemalocation="http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">' . PHP_EOL;

while ($row = $data1->fetch_assoc()) {
    $encoded_url = $row['urls'];
    $xml_str .= '<url>' . PHP_EOL;
    $xml_str .= '<loc>' . htmlspecialchars($encoded_url) . '</loc>' . PHP_EOL;

    while ($row2 = $data2->fetch_assoc()) {
        $encoded_url2 = $row2['name'];
        $xml_str .= '<loc>' . htmlspecialchars($encoded_url2) . '</loc>' . PHP_EOL;
    } // end child loop
    // reset $data2 result pointer
    $xml_str .= '<lastmod>'. date(DATE_ATOM, time()) .'</lastmod>' .PHP_EOL;
    $xml_str .= '</url>' . PHP_EOL;
    $data2->data_seek(0);
}
$xml_str .= '</urlset>';
$dom = new DOMDocument('1.0', 'iso-8859-1');
$dom->preserveWhiteSpace = false;
$dom->loadXML($xml_str);
echo $xml_str;
$dom->save('./sitemap.xml');

Desired Output:

category_table 1

something_table 1

category_table 2

something_table 2

category_table 3

something_table 3

category_table 4

something_table 4

. . . .

Note:

2 tables doesn't have the same result (No. of result) .

Dharman
  • 30,962
  • 25
  • 85
  • 135
angtesters
  • 13
  • 1
  • 5
  • It is a very bad idea to use `die(mysqli_error($$conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) – Dharman Dec 09 '20 at 21:36
  • It would be much easier to do it using PDO. Why are you using PDO yet? – Dharman Dec 09 '20 at 21:40
  • The solution you are looking for us probably SQL joins, but it's unclear what exactly is your data. How is it connected? I don't understand the output. It looks like you access the rows based on some criteria which you have not shared with us. – Dharman Dec 09 '20 at 21:42
  • about die mysqlierror ok i will delete it .. and abou PDO i think i can't execute multiple query with PDO .. i tried to do that with union select with concat , it works but can i do it with separate queries without join or concatenation ? .. thanks ! – angtesters Dec 10 '20 at 10:48
  • You can do with PDO everything you can with mysqli and more. – Dharman Dec 10 '20 at 11:11

0 Answers0