Jump to content

saynotojava

Members
  • Posts

    38
  • Joined

  • Last visited

Posts posted by saynotojava

  1. 21 minutes ago, Barand said:

    Those two queries are totally unrelated, so, for each record in the first set of results you produce an identical set of results from the second query.

    Either confirm that is what you want to do or tell us what is you really want to do - or i'm giving up.

    They share common values, like Value1 is same as Value3  and Value2 is same as Value4. They also share datetime column which is same.

    And here is some data output example::

    Table1                                 Table2

    Value1 A Value2 fror  Value3 A Value4 fror

    Value1 B Value2 frbo

    Current code counts number of each duplicates, in this case first query number of values A/fror in Table1 , and same in Table2. In case table2 does not contain anything .then i show it as zero.

    So end result look like this:

    A Fror 500 2 -In first table there 500  entries with value A Fror, while in second two

    B frbo 100 0-In first table there is 100 entires with value B frbo, while second does not contain anything

  2. Ok here is full currently working code with 2 queries, and goal is to have only one query instead.

    $result = $conn->query("SELECT `Table1`.Value1, `Table1`.Value2, count(*) AS count,SUM(Co) AS sum FROM  `Table1` WHERE  `Table1`.`Dt` between '2019-08-27 00:00:00'
     AND '2019-08-27 23:59:59' GROUP BY `Table1`.Value1, `Table1`.Value2  ; ");
    
    
    echo'
    	<table class="zui-table zui-table-highlight-column">
        <thead>
            <tr>
                <th>Value1</th>
                <th>Value2</th>
                      </tr>
        </thead>
        <tbody>';
      while($row = $result->fetch_assoc()) {
    
      $str = $row['Value1'];   
      $str = strtoupper($str);
      
    echo"
    	   <tr>
                <td>echo $str</td>
                <td>echo {$row['Value2']}\"></td>
                <td>{$row['count']}</td>
                <td>
    ";
    $resultb = $conn->query("SELECT `Table2`.Value3, `Table2`.Value4, SUM(Payout) AS count , count(*) AS mu FROM  `Table2` WHERE `Table2`.`Dt` between '2019-08-27 00:00:00' AND '2019-08-27 23:59:59' GROUP BY `Table2`.Value3, `Table2`.Value4  ; ");
    if ($resultb->num_rows > 0) {
    	 while($rowb = $resultb->fetch_assoc()) {
    
    
    $amo=substr($rowb['count'], 0, 5);
    echo $amo;
    $numb = $rowb['mu'];
     	}
    }
    else {
    
    }
    echo "
                </td>
                 <td>";
    
    
                echo" </td>
                      <td></td>
                          <td>
    ";
    
    
    echo"
                          </td>
                          <td>";
    
                          echo"</td>
                          <td>
    ";
    
    
    
    
    
    
    
    
    echo"
                          </td>
              </tr>
    "; 
    } echo'
      </tbody>
    </table>

     

  3. 1 minute ago, Barand said:

    Are those the two queries you mentioned, or two of your attempts at combining the queries.

    Your first references Visits.Va2  yet there is no mention of a Visits table.

    You also mention LEFT/Right JOIN and UNION but they seem to be absent too, as is any kind of JOIN.

    Why is the second query being run in a loop?

    In other words, can you provide a better description of what you are trying to, preferably with the table structures.

    Visits thing was error, updated post to properly show first query(it's table1 instead). Also, Using Left/Right Union was one was with which i tried to solve it, does not need to be same method but it seems it does what i trying to accomplish. At the moment i loop query with table2 , and when there is no result, i output it as zero.

  4. I have two mysql queries , where currently  i run second query by loop, so would like to reduce loading time by making single query. However, it seems there is some issue with WHERE clause which i can put it into only one query when using LEFT/RIGHT JOIN with Union and i use WHERE in both queries.

    Here is queries:

    SELECT `Table1`.Va, `Table1`.Va2, count(*) AS count,SUM(Co) AS sum FROM  `Table1` WHERE  `Table1`.`Dt` between '2019-08-27 00:00:00'
     AND '2019-08-27 23:59:59'  GROUP BY `Table1`.Va, `Table1`.Va2 
    
    SELECT `Table2`.Va, `Table2`.Va2, SUM(Payout) AS count , count(*) AS mu FROM  `Table2` WHERE `Table2`.`DateTime` between '2019-08-27 00:00:00' AND '2019-08-27 23:59:59'  GROUP BY `Table2`.Va, `Table2`.Va2

     

  5. What? You want to use your users as laboratory rats for potentially malicious sites? Are you kidding?

     

    If you don't know if a site is trustworthy, then don't redirect to that site. What kind of application is this, anyway?

     

    You generally cannot “see what a user is doing on another site”, and with good reason. Would you want us to see your PayPal account, your e-mails, your private Facebook messages?

     

    Let me put this way - let's say you are webmaster, and i am advertiser. I give you a link to advertise on your site, you checked it and it is fine. But i added a setting to redirect to bad url which is different then your browser, and wouldn't you want to know what is really happening? I dont need to know what exactly is user doing it after url is loaded, but i do need to know does he see same thing when i load url in my browser.

  6. Purpose of this is to detect malware/bad links in redirect url - when you have a redirect url which redirect based on criteria, url might be fine when you check with your browser, but could be malware/bad link with other browser. Method does not need to be iframe(this is just idea to do it), nor user need to stay on site, i just need to see what happening with user when he leave site.

    At the moment i need to use different browsers manually to do such checks, but when you have tons of links to check it become a tedious job.

  7. How do i record with php when user loads external url which redirect , what urls's he loaded by using invisible iframe(so the user still stay at my site) ?

    I do know this can be done with curl and i tested it, but problem with curl how it access url redirect by using server, while it should access as user.

     

  8.  

    From what you have described you would want four tables (see attached model)

    • video
    • tag
    • video_tag (to store the tags for each video)
    • click (to log each click of a video's tag)

     

    But where is Tags in Video? That one is in list form (Dogs,Cats) and cannot be changed as it used already. That look fine if i would start with fresh table structure but i need to find solution to make it work with Tags in list form at table Video.

  9. Ok let me give entry example:
    Table Name: Videos
    VideoId  - 12345
    Tags - Dogs,Cats
    Clicks - 150
    So this is current table, where problem is when i order by Clicks, there will be same video showed on both tags(tags is same thing as categories). Video should have separate clicking statistic for each Tag listed in Tags, so there could be different videos listed when clicking on Tag ordered by clicks.
    I was thinking about removing Clicks from structure  of table videos, then creating table called Clicks which will contain Videoid, Tag and Clicks.
    So Videoid should be same value as relevant videoid in table Videos, so we can know to which video entry is related clicking statistic.
    And here is example:
    Table name:clicks
    Videoid:12345
    Tag:Dogs
    Clicks:150
    Hopefully now is more clear.Can post more examples if needed.

  10. I have a table which currently contain data like Videoid,Tags,and clicks.

    As table Tags contain multiple tags, this causing problem where a same video will show a top clicked video on each tag section which it contain.

    What it should be,is how each tag have it's own clicks.

    At the moment solutions which i have but dont find it suitable are following:

    1.To put a single tag instead list of tags, this is problem as then i would have to copy same data for every tag

    2.To create another table called Clicks, and everytime when it is clicked on video in new tag, copy all relevant data from first table and then call UNION query.

    Problem with this how we also have duplicates(tho alteast not big as in first case, as new data is inserted only when click occur) and

    second is how same videos could show, as first it will show one which contain click stats and single tag, and then same video which dont contain click stats from first table.

     

    Closest to solution was using CROSS JOIN query, but  problem with that query is how is simply pulling all data id by id, and it need to be linked,

    for example both tables  have Videoid table which is same, and then order by Clicks which is in second table.

  11. Ok i found a proper query:

    SELECT MAX(POSITION)

    but it seems something is bugged here:

    ,when it reach number 10(i set a counter +1 on each new entry),for some reason it output number 9 as biggest number,not 10.And another thing,i have query to order by that column where i stored those numbers,but number 10 it is not last,for some reason it is sorted right after 1.

  12. I have following two dimensional array:

    $shop = array( array( Title => "rose",
                          Price => 1.25,
                          Number => 15
                        ),
                   array( Title => "daisy",
                          Price => 0.75,
                          Number => 25,
                        ),
                   array( Title => "orchid",
                          Price => 1.15,
                          Number => 7
                        )
                 );

    Where i want to unset/remove one array in it,i do that with following:

    $count=3;

    unset ($shop[0]);

    And i output array after that with this:

    for ($row = 0; $row < $count; $row++)
    {
        echo $shop[$row]["Title"]." costs ".$shop[$row]["Price"]." and you get ".$shop[$row]["Number"];
        echo "<br />";

    }
     

    The problem is how with unset array is not fully removed,it just show as empty value,so while it should output array like this:

    daisy costs 0.75 and you get 25
    orchid costs 1.15 and you get 7

    It shows this:

    costs and you get
    daisy costs 0.75 and you get 25
    orchid costs 1.15 and you get 7

    And if i output array with print_r,then it shows properly but i need it with echo format.

  13. For each checkbox name and domain you also need to assign them the same unique key.

    If you don't do this then the checkbox values will not pair up with the hidden fields keys,and you'll get miss matched results.

     

    When you generate the checkboxes do

    	echo '<tr><td>'.$row['Domain'].'</td>
    	      <td>
    	        <input type="hidden" name="a['.$i.']" value="'.$row['Domain'].'">
    	        <input type="hidden" name="b['.$i.']" value="N">
    	        <input type="checkbox" name="b['.$i.']" value="Y">
    	      </td></tr>'; 
    	$i++; // increment unique id
    

    Before the loop that echo's the hidden fields/checkboxes add $i = 0; to instantiate the counter

     

    Now your foreach loop should work as expected

    Yep,it works.

  14. And now i figured out how to insert N with making an td line like this:

    echo '<td>' .$row['Domain'].'</td><td><input type="hidden" name="a[]" value="'.$row['Domain'].'"><input type="checkbox" name="b[]" value="Y"><input type="hidden" name="b[]" value="N"></td>';

     

    But this causing strange bug-when i click on two values,first value is correctly added,but second Y doesn't go domain which i clicked,it goes to domain right after that one for some reason.And that repeats for every further selection,Y always write to domain after the one which i clicked.

  15. OK i found partial solution:what i select i get properly inserted with Y,nonselected get inserted as well,but it doesn't show N on that field for some reason,it stay empty.

    Here is changes which i did:

      echo '<td>' .$row['Domain'].'</td><td><input type="checkbox" name="a[]" value="'.$row['Domain'].'"></td>';

    replaced with

    echo '<td>' .$row['Domain'].'</td><td><input type="hidden" name="a[]" value="'.$row['Domain'].'"><input type="checkbox" name="b[]" value="Y"></td>';

      foreach($_POST['a'] as $check) {

    replaced with

      foreach (array_keys($_POST['a']) as $key)

    and added after foreach :

       $check = $_POST['a'][$key];
       $bleh = $_POST['b'][$key];

    $sql =<<<EOF
    INSERT INTO "Urls" ("Domain", "Url", "Anchor", "User","AdminDisplay","UserDisplay")
    VALUES ('$check', '', '', '$a','Y','');
    EOF;

     }

    replaced with

    $sql =<<<EOF
    INSERT INTO "Urls" ("Domain", "Url", "Anchor", "User","AdminDisplay","UserDisplay")
    VALUES ('$check', '', '', '$a','$bleh','');
    EOF;

     

    Maybe i should just leave it as it despise there is no N written,after all it saves some space.But as after this i need to make update and delete part,maybe it will be required no matter what.

  16. I have a code which do following:select data from database,and when you click on it,it will insert only those checkboxes which you selected.

    But the problems is,i want non checked checkboxes to be executed as well,they would execute same query except one difference-one field in structure should write  n instead y then.

    Here is code:

    Selecting part:

    echo'<table><tr>';


        while($row = $ret->fetchArray(SQLITE3_ASSOC) ){

          echo '<td>' .$row['Domain'].'</td><td><input type="checkbox" name="a[]" value="'.$row['Domain'].'"></td>';
       //  echo '<td>A</td><td><input type="checkbox" name="a[]" value="n"></td></tr>';
    if ($i % 10 == 0)
                 echo '</tr><tr>';
          $i++;
    }
    echo'</tr></table>
    <br/> ;

     

    Inserting Part:

    if(!empty($_POST['a'])) {
        foreach($_POST['a'] as $check) {
           
    $sql =<<<EOF
    INSERT INTO "Urls" ("Domain", "Url", "Anchor", "User","AdminDisplay","UserDisplay")
    VALUES ('$check', '', '', '$a','Y','');
    EOF;

     }

  17.  

    Try replacing while loop using below code

    $i = 1;
    echo '<tr>';
    while($row = $ret->fetchArray(SQLITE3_ASSOC) )
    {
          echo '<td>' .$row['Domain'].'</td><td><input type="checkbox" name="a[]" value="n"></td>';
          if ($i % 5 == 0)
                 echo '</tr><tr>';
          $i++;
    }
    echo '</tr>';
    

    Works as charm,thanks.I know it trick is in increment and if but didn't know how exactly to put it.

  18. I have currently this code:

    echo'<table border="1">';


        while($row = $ret->fetchArray(SQLITE3_ASSOC) ){

          echo '<tr><td>' .$row['Domain'].'</td><td><input type="checkbox" name="a[]" value="n"></td></tr>';


    }
    echo'</table>';

    Which will output query from database and it will show output in that table format.But the problem is, when there is a lot of info pulled of,it look bad as a lot of space in unused then,so

    i want to make it every fifth output to show in new line(compared to current where every output goes to new line).Also,output does'nt need to be with tables,i just want to make it look normaly aligned.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.