Jump to content
Larry Ullman's Book Forums

Paul

Members
  • Posts

    147
  • Joined

  • Last visited

  • Days Won

    1

Posts posted by Paul

  1. Hiya,

    HartleySan. When you have time I would be interested in trying to understand why it's needed for the EXISTS but not the IN. The IN syntax makes perfect sense to me where the EXISTS syntax doesn't. But please don't spend ages as you've spent a lot of time on this already.

     

    Margaux. I've just found the Sams book on Amazon. As per the discussion above, in the abscence of a good on-line resource would you recommend it as a good old-fashioned paper reference? It's £31, but it's worth it if it's good. Probably a silly question but I assume that SQL syntax from an SQL book is the same syntax as used in MySQL in a PHP query?

     

    Has anyone else used it?

     

    Cheers

    Paul

  2. My apologies HartleySan, it appears that aliases are more significant than I gave them credit for. I thought that the assignment of aliases was purely a cosmetic renaming.

     

    I note from your post above that aliases create tables in memory. However what's the purpose of the 'AND c1.artist_id = c2.artist_id' part? At first glance if the subquery is performed first then c1 would be unknown, as that's not created until the second part of the query.

  3. Hiya,

    The following worked a treat:

    $q = "SELECT artist_id FROM cat_artist WHERE artist_id NOT IN (SELECT artist_id FROM cat_artist WHERE category_id != {$_SESSION['category_id']})";

     

    The NOT IN conditional and the concept of subqueries was the key for me with this question, which is essentially what the 'double negative' approach used. A good learning curve, this one. Thanks for that.

     

    However when replacing the IN part with EXISTS, as follows:

    $q = "SELECT artist_id FROM cat_artist WHERE NOT EXISTS (SELECT artist_id FROM cat_artist WHERE category_id != {$_SESSION['category_id']})";

     

    It didn't error, it just didn't return anything.

     

    Thanks for all your help.

    Paul

  4. Hi all,

    I've just got back from holiday and am trying to use HartleySan's ideas above in a realworld query and the error I'm getting is that of an invalid use of the GROUP clause. To summarise what I'm trying to do.

     

    Firstly I want to select the artist_id from cat_artist table where the category_id equals the category to be removed. The user has chosen the category to be removed and it's stored in a session. This is the subquery in the statement below. The sub query works because I tested it seperatly first.

     

    Then I want to take these artists_ids ONLY and select the artist_ids that belong to only 1 single category_id (these are the ones to be removed, as opposed to those that belong to mutiple categories, which don't). So I only want artist_ids that belong solely to the category that needs to be removed.

     

    Once I have the correct artist_ids in an array I plan to use a foreach loop to iterate through them and delete them. But I need the correct artists in there in the first place.

     

    I tried the following:

     

    $q = "SELECT artist_id FROM cat_artist WHERE COUNT(category_id = 1) AND EXISTS (SELECT artist_id FROM cat_artist WHERE category_id = {$_SESSION['category_id']})";

     

    The error message is an invalid use of the GROUP clause. My guess is that I'm using the COUNT syntax incorrectly.

     

    Any help would be really great.

     

    Thanks

    Paul

     

    PS. Can anyone recommend an SQL resource. I tried the MYSQL website but I find it a bit heavy and technical. Something like the PHP manual, which is a bit more 'readable' would be ideal.

  5. Hartley San,

    Brilliant. I won't get a chance to test it until later but I didn't realise that you could do a select within a select, that's the answer.

     

    Would I be correct in thinking that the second SELECT (the one in brackets) is done first and then the IN conditinal says 'take these values only' and applies them to the first SELECT.

     

    This is one that's definately worth remembering.

     

    Cheers

    Paul

    PS No need to apologise I really appreciate the help even if it doesn't work.

  6. Hi all,

    I have a table that looks like this:

     

    category_artist_id---------category_id-----------artist_id

    -------39------------------------------7-----------------------22-----

    -------40------------------------------11----------------------23----

    -------41------------------------------11----------------------24----

    -------42------------------------------11----------------------25---

    -------43------------------------------11----------------------26---

    -------44------------------------------9-----------------------26---

     

    I already know the category_id, in this case 11 as the user has picked thisfrom a form. I want to write a query that would return the bottom row (44), i.e. one that returns a cat_artist_id where an artist_id has more than one category_id. In this case artist_id 26 has another category apart from 11 (it's 9). The idea is that artist_id 26 won't be deleted because they use another category as well as 11.

     

    Even as I write this I think that this must be simple but I can't get it to work in 1 query. Basically what I want to say is:
     

    "select artist_id from this table where category_id = 11 THEN using these artists_id's select artist_id where category_id does not equal 11".


    It looks above like I need 2 queries to get the result I want. Does this indicate that I've not structured my tables correctly.


    Should it be this complicated?

     

    Thanks

    Paul

     

  7. Larry,

    Thanks for the reply. I tried a different approach by using the code below:

     

    if(isset($_COOKIE['cms_site']))
        {
            if(($_COOKIE['cms_site']) == 'Gallery')
            {
                require(GalleryMYSQL);
            }
            else
            {
                require(PrintsMYSQL);
            }        
        }
        else
        {
            $url = BASE_URL . 'login.php';
                
            header("Location: $url");
        }

     

    This worked OK.

     

    Rather than use all this code on every page that needs a DB connection I tried wrapping it in a function as follows:

     

    function set_con ()
    {
        if(isset($_COOKIE['cms_site']))
        {
            if(($_COOKIE['cms_site']) == 'Gallery')
            {
                require(GalleryMYSQL);
            }
            else
            {
                require(PrintsMYSQL);
            }        
        }
        else
        {
            $url = BASE_URL . 'login.php';
                
            header("Location: $url");
        }
    }

     

    Same code, just wrapped in a function. I then called the function in the same file instead of all the above code, as follows:

     

    set_con();

     

    It failed to make a connection. I would've thought wrapping it in a function would make no difference. It's not a show stopper, just curious.

     

    Any ideas?

    Thanks

    Paul

     

  8. Morning all,

    I have created the following 2 constants in my config file:

     

    //Gallery DB for CMS
    define('GalleryMYSQL', 'C:\Users\Paul\Documents\Work\Projects\Work in Progress\Northern Lights (Feb 2013)\testing\g_mysql_con.php');

    //Prints DB for CMS
    define('PrintsMYSQL', 'C:\Users\Paul\Documents\Work\Projects\Work in Progress\Northern Lights (Feb 2013)\testing\p_mysql_con.php');

     

    Which one is used is dependant on where the user logs in. When they log in a cookie is created that stores either 'Gallery' or 'Prints'. I then have the folowing code to call the correct constant:

     

    $connect = $_COOKIE['cms_site'].'MYSQL';   
    require($connect);

     

    When I run the file I get the following error:

     

    An error has occurred in script 'C:\xampp\htdocs\Northern
    Lights\public_html\cms\add_category.php' on line 39:
    require(GalleryMYSQL) [function.require]: failed to open stream: No such file or directory


         Date/Time: 2-28-2013 09:39:32


         . print_r(Array, 1) .

     

    I've tested the path to the constant, it opens the correct file. The error message above even contains the correct name for the constant yet it says it doesn't exist. I'm stumped. Any help would be appreciated.

     

    Cheers

    Paul

     

  9. Hi all,
    I have defined a user defined  function which is located in a config.php include file that is the first file in every page:

     

    function set_cms()
    {
        $set_cms_site = 'Gallery';
        
        setcookie ('set_cms_site', $set_cms_site);
        
        echo '<span class="banner_colour">' . $_COOKIE['set_cms_site'] . '</span>';
        
    }

     

    I then call it as follows:

     

    echo'
    <div id="page_heading">
    <h2 class="centre_text">';
    //Call function to display the CMS site that has been logged into
    set_cms();

    echo'
    : Add new category</h2>
    </div>
    ';

     

    When I load the page it comes up with 'Undefined index: set_cms_site '.

     

    However when I reload the page it works. I would guess that I'm doing something in the wrong order and the whole process isn't loading the first time but it is the second time.

     

    Thanks

    Paul

     

  10. Hi all,

    Vince, or anyone else.

     

    Has anyone managed to send or receive emails to or from external email address using the above setup. As I've said above I can send 'internally' from a localhost adress to a local host address but as soon as I try a message with an external address nothing is received. If I send from my gmail address it goes in the gmail sent box but nothing turns up on localhost. I've created a postmaster@localhost.com user and alias because I read that email accounts must have one of these addresses.

     

    So far I've muddled through without needing this functionality but now I can't avoid it any more.

     

    Cheers

    Paul

  11. Edward,

    Thanks for the response but I got sidetracked and have just now returned to this problem.

     

    If I use Firebug to check the HTML that has been generated by my foreach loop above then each iteration has the correct image within the modal content div. If I use a while loop then for what am I using it? If the foreach loop was only creating a single modal content div in the first iteration then I would understand why only the first one is working but that's not the case.

     

    The CSS and the JS are both external files so when the link is clicked the 'openModal' function should run (as it does with the first row of data) and display it's relevant model content.

     

    If you have 2 minutes would you walk me through the logic of what's not working.

     

    Cheers

    Paul

  12. Antonio,

    Thanks very much it worked fine.

     

    I just needed to amend it slightly and add an extra bit of code as follows:

     

    if ($row['snip_count'] == 0)

    {

    echo'<p>There are no snippets displayed in this price range</p>';

    }

    else

    {

    echo '<p>How many: '. $row['snip_count'] .'</p>';

    }

     

    This is because the count could be zero. If this record is zero the 'else' part above wouldn't kick in, it would return 'How many' as 0.

     

    I've also learned what '===' is, although when I get 2 minutes I need to look up the difference between '===' and '=='.

     

    Cheers

    Paul

  13. Hi everyone,

    I'm getting the following error message:

     

    "mysql_fetch_array() expects parameter 1 to be resource, object given"

     

    with the following code:

     

    $q = "SELECT COUNT(snippet_ref) FROM snippets WHERE (display = 'yes' AND price < 8)";

     

    $r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

     

    $p8_displayy = mysql_result($r, 0);

     

    if ($p8_displayy >= 1)

    {

    echo'

    <p>How many:' . $p8_displayy . '</p>

    ';

    }

    else

    {

    echo'

    <p>There are no snippets displayed in this price range</p>

    ';

    }

     

    mysqli_free_result($r);

     

    I must admit to being a bit confused as to whether to use mysql_result, mysql_num_rows or a while loop with Mysqli_fetch_array. The one above seems to be the oist logical to me as a count function presumably doesn't return rows, just a number.

     

    I've triple checked the table and column names. It should return '5'.

     

    Cheers

    Paul

  14. Leo,

    Have a look at the following code that I'm using to resample an image that a user is uploading. It appears to work OK. There's lots of superfluous code as I was checking along the way that it worked. The script picks up the large uploaded file after it's been moved from it's temporary location, does it's stuff, then saves it back in the same directory.

     

    $filename = $upload_name;

     

    echo'

    <img src="' . $filename . '" ><br/>

    ';

     

    // Set a maximum height and width

    $width = 500;

    $height = 500;

     

    echo'

    <p>Required image image width = ' . $width . ' and height = ' . $height . '</p>

    ';

     

    // Get new dimensions

    list($width_orig, $height_orig) = getimagesize($filename);

     

    echo'

    <p>Current image width = ' . $width_orig . ' and image height = ' . $height_orig . '</p>

    ';

     

    $ratio_orig = $width_orig/$height_orig;

     

    if ($width/$height > $ratio_orig) {

    $width = $height*$ratio_orig;

    } else {

    $height = $width/$ratio_orig;

    }

     

    echo'

    <p>New image width = ' . $width . ' and height = ' . $height . '</p>

    ';

     

    // Resample

    $image_p = imagecreatetruecolor($width, $height);

    $image = imagecreatefromjpeg($filename);

    imagecopyresampled($image_p, $image, 0, 0, 0, 0, $width, $height, $width_orig, $height_orig);

     

    // Save the image

    imagejpeg($image_p, '../uploaded_snippets/' . $name.'.jpg');

     

    // Free up memory

    imagedestroy($image_p);

     

    echo'

    <img src="../uploaded_snippets/' . $name.'.jpg" ><br/>

    ';

     

    ?>

     

    Hope this helps.

     

    Cheers

    Paul

  15. Hi leo and margaux,

    Please correct me if I'm wrong but what you are doing here doesn't actually resize the image from the point of view of a browser downloading it...does it?

     

    If leo uses the width and height variables to change the image attributes in the img tag then the browser will still need to download the full size image. I'm trying to do a similiar thing and have just started experiementing with the functions imagecopyresampled() and imagecopyresized().

     

    Hope this helps.

     

    Cheers

    Paul

  16. Hi all,

    An update. I changed the code this morning and tried the following:

     

    $file_allowed = array('image/JPG', 'image/pjpeg', 'image/jpeg');

     

    if (in_array($_FILES['upload_snippet']['type'], $file_allowed))

    {

    //Generate a temporary name for the uploaded image that will be changed to the snippet name latet

    $temp_name = '../uploaded_snippets/' . md5($_FILES['upload_snippet']['name']);

     

    //Move the file from it's temporary location to the uploads directory

    if (move_uploaded_file($_FILES['upload_snippet']['tmp_name'], $temp_name))

    {

    echo'

    <p>It worked</p>';

    }

    else

    {

    echo'

    <p>It didn\'t worked</p>';

    }

    }

    else

     

    It didn't work.

     

    I then changed it to an absolute path, as follows:

     

    //The file should be a JPG

    $file_allowed = array('image/JPG', 'image/pjpeg', 'image/jpeg');

     

    if (in_array($_FILES['upload_snippet']['type'], $file_allowed))

    {

    //Generate a temporary name for the uploaded image that will be changed to the snippet name latet

    $temp_name = 'C:\Users\Paul\Documents\Work\Projects\Work in Progress\Textures\code\uploaded_snippets\\' . md5($_FILES['upload_snippet']['name']);

     

    //Move the file from it's temporary location to the uploads directory

    if (move_uploaded_file($_FILES['upload_snippet']['tmp_name'], $temp_name))

    {

    echo'

    <p>It worked</p>';

    }

    else

    {

    echo'

    <p>It didn\'t worked</p>';

    }

    }

    else

     

    And this worked.

     

    Unfortunately I have no idea why. If you compare the absolute paths as per my last post I don't understand why the first bit above doesn't work.

     

    Cheers

    Paul

  17. Hi all,

    I am getting the following error message when trying to move an uploaded file:

     

     

    An error has occurred in script 'C:\xampp\htdocs\Textures\web\admin_snippets_new.php' on line 64: move_uploaded_file(../uploads/green.jpg) [function.move-uploaded-file]: failed to open stream: No such file or directory

    Date/Time: 11-9-2012 16:07:22

    . print_r(Array, 1) .

     

    An error has occurred in script 'C:\xampp\htdocs\Textures\web\admin_snippets_new.php' on line 64: move_uploaded_file() [function.move-uploaded-file]: Unable to move 'C:\xampp\tmp\php8BD9.tmp' to '../uploads/green.jpg'

    Date/Time: 11-9-2012 16:07:22

    . print_r(Array, 1)

     

    Here's the code in question:

     

    if (isset($_FILES['upload_snippet']))

    {

    //Check to see if there are any upload errors

    if (!empty($_FILES['upload_snippet']['error']))

    {

    if ($_FILES['upload_snippet']['error'] = 4)

    {

    echo'

    <p class="error_msg">There is a problem with the file upload, there has been no file chosen.</p>

    ';

    }

    else

    {

    echo'

    <p class="error_msg">There is a problem with the file upload, talk to Paul.</p>

    ';

    }

     

     

    }

    else

    {

    //The file should be a JPG

    $file_allowed = array('image/JPG', 'image/pjpeg', 'image/jpeg');

     

    if (in_array($_FILES['upload_snippet']['type'], $file_allowed))

    {

    //Move the file from it's temporary location to the uploads directory

    move_uploaded_file($_FILES['upload_snippet']['tmp_name'], "../uploads/{$_FILES['upload_snippet']['name']}");

     

    }

    else

    {

    echo'

    <p class="error_msg">Only JPG files are allowed.</p>

    ';

    }

    }

    }

    echo print_r($_FILES);

     

    The above array that I've echoed is:

     

    Array ( [upload_snippet] => Array ( [name] => green.jpg [type] => image/jpeg [tmp_name] => C:\xampp\tmp\php8BD9.tmp [error] => 0 => 3263 ) ) 1

     

    As you can see there are no errors in the file upload. The uploads directory is one level up from the coded file, in the same place as the mysql connect script, so outside the web directory. I've checked the folder itself in Windows 7, right clicked and checked that the security permissions are all set to 'allow'. The directory name is the same as the code above, no typo's that I can see.

     

    Not sure where else to check. Any ideas would be appreciated.

     

    Cheers

    Paul

  18. Hi all,

    Ignore me I think I've sorted it. Radio buttons can only have one 'on' at a time, that's why the foreach loop only has the last one updated. The reason it had both checked was because I gave the radio buttons a different name instead of the same name (which would have toggled).

     

    I need to have a total rethink now about how I do what I'm trying to do, which is to give the user the ability to amend the displayability (if that's a word!).

     

    Thanks

    Paul

     

    PS. I'm still interested in what a MVC is though.

  19. Hi all,

    Thanks for the responses, I got the syntax error to go away by inserting php tags, it does seem a cleaner way to do it. Although for simpler stuff like the price, name and description it seems easier to just concatenate.

     

    However the if statement doesn't work. Have a look at the whole foreach loop:

     

    foreach ($snippets_8 as $snippets_name => $snippets_list)

    {

    echo'

    <tr>

    <td><img src="images/snippets/'. $snippets_list['image'] .'.jpg" width="80px" height="80px" ></td>

    <td>'. $snippets_list['price'] . '</td>

    <td>'. $snippets_list['name'] . '</td>

    <td>'. $snippets_list['description'] . '</td>'

    ?>

    <td><input name="display_y" type="radio" value="Yes" checked="<?php if($snippets_list['display']='y'){echo 'checked';}?>"/></td>

     

    <td><input name="display_n" type="radio" value="No" checked="<?php if($snippets_list['display']='n'){echo 'checked';}?>" /></td>

    <?php

    echo'

     

    <td><input name="remove" type="radio" value="' . $snippets_name . '" /></td>

    </tr>';

    }

     

    I've checked the $snippets_8 and $snippets_list arrays and they appear to be OK (the snippets_list array only showed the last item) and the list outputs OK. However the only radio buttons that are checked are both display yes and display no on the last item in the list, the rest are blank. In fact, what should happen is that the first six are checked yes and the last one checked no. I also removed the quotes from 'y' and 'n' in the if statement in case it was treating it as a boolean rather than a string. Lots of errors with that one.

     

    Cheers everyone.

    Paul

     

    PS, Edward, what's a MVC?

×
×
  • Create New...